Merge contiguous lines unless they intersect with a point

Question

I have two PostGIS tables:

• roads have an id, a name, and a geom (line).
• nodes have an id and a geom (point). They represent road tips (intersections or dead-ends).

I would like to store for each road a begin node and an end node. I know their geometries match correctly:

SELECT ST_Intersects(
    (SELECT geom FROM nodes WHERE id = 995),
    (SELECT geom FROM roads WHERE id = 421)
);
-- => true

However, I have way too many roads, and not enough nodes. Here's why:

Red lines are road sections, yellow squares at their tips.

Red dots are nodes. Should be the only tips.

Thus I want to merge redundant roads (have the same name and intersect at one tip and don't intersect with a node at this tip). These road sections also ST_Intersects() correctly. How can I perform this merge with PostGIS (SQL)?

---

I decided to proceed in two steps:

1. Merge all intersecting roads with the same name
2. Split merged roads with the points

I successfully merged the roads using this answer. First I created a new table, mroads (merged roads).

CREATE TABLE mroads ( -- Merged roads
    gid SERIAL,
    name varchar(10),
    CONSTRAINT pk_troncons PRIMARY KEY (gid)
);
ALTER TABLE mroads
    ADD COLUMN geom_tmp geometry(GeometryCollection),
    ADD COLUMN geom geometry(MultiLineString, 2154);
CREATE INDEX idx_mroads ON mroads USING GIST(geom);

I used a temporary geom_tmp column, since the ST_ClusterIntersecting method returns collections, and I don't want collections, so I'll have to process them later. Then I merged the roads:

INSERT INTO mroads (name, geom_tmp)
SELECT name, unnest(ST_ClusterIntersecting(geom))
FROM roads GROUP BY name;

I ran into problems about how collections didn't have a SRID, and some other things. My tinkering resulted in this:

-- SRID for my data is 2154 (Lambert-93 for metropolitan France)
SELECT UpdateGeometrySRID('public', 'mroads', 'geom_tmp', 2154)
-- ST_ClusterIntersecting did not actually merge geometries,
-- it regrouped them in a collection. We ST_LineMerge this collection.
UPDATE mroads SET geom = ST_Multi(ST_LineMerge(ST_CollectionExtract(geom_tmp, 2)));
ALTER TABLE mroads DROP COLUMN geom_tmp;

(Trivia: I had 756k rows, now there are only 91k of them.) Now, I want to split mroads wherever they intersect with nodes, unless it's at the very beginning or end of the line. I expect to produce more tuples.