Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE example
(
    node text,
    connections text[],
    nodes_in_chain text[]
);
INSERT INTO example VALUES
('a', ARRAY['a','b'],         null),
('b', ARRAY['a','b','c','d'], null),
('c', ARRAY['b','c'],         null),
('d', ARRAY['b','d'],         null),
('e', ARRAY['e','f'],         null),
('f', ARRAY['e','f'],         null);

SELECT * FROM example;
6 rows affected



node
connections
nodes_in_chain


a
{a,b}
null

b
{a,b,c,d}
null

c
{b,c}
null

d
{b,d}
null

e
{e,f}
null

f
{e,f}
null


SELECT e1.node, 
       e1.connections, 
       COALESCE(e2.connections, e1.connections) nodes_in_chain
FROM   example e1
LEFT JOIN   example e2
       ON e2.node <> e1.node
       AND (e1.connections <@ e2.connections);


node
connections
nodes_in_chain


a
{a,b}
{a,b,c,d}

b
{a,b,c,d}
{a,b,c,d}

c
{b,c}
{a,b,c,d}

d
{b,d}
{a,b,c,d}

e
{e,f}
{e,f}

f
{e,f}
{e,f}


DELETE FROM example WHERE node = 'f';
INSERT INTO example VALUES
('f', ARRAY['e','f','g'],     null),
('g', ARRAY['f','g','h'],     null),
('h', ARRAY['g','h'],         null);
1 rows affected

3 rows affected

WITH RECURSIVE al (dst, src) AS
(
  SELECT e1.node, e2.node
  FROM   example e1
  JOIN   example e2
         ON e1.node = any(e2.connections)	
), tc (dst, src) AS
   (
     SELECT dst, src FROM al
     UNION
     SELECT a1.dst, a2.src
     FROM   al as a1 
     JOIN   tc as a2 
            ON a1.src = a2.dst
   )
   SELECT   src, array_agg(DISTINCT dst ORDER BY dst) AS nodes_in_chain
   FROM     tc
   GROUP BY src;


src
nodes_in_chain


a
{a,b,c,d}

b
{a,b,c,d}

c
{a,b,c,d}

d
{a,b,c,d}

e
{e,f,g,h}

f
{e,f,g,h}

g
{e,f,g,h}

h
{e,f,g,h}


SELECT e1.node, 
       e1.connections, 
       COALESCE(e2.connections, e1.connections) nodes_in_chain
FROM   example e1
LEFT JOIN   example e2
       ON e2.node <> e1.node
       AND (e1.connections <@ e2.connections);


node
connections
nodes_in_chain


a
{a,b}
{a,b,c,d}

b
{a,b,c,d}
{a,b,c,d}

c
{b,c}
{a,b,c,d}

d
{b,d}
{a,b,c,d}

e
{e,f}
{e,f,g}

f
{e,f,g}
{e,f,g}

g
{f,g,h}
{f,g,h}

h
{g,h}
{f,g,h}
node	connections	nodes_in_chain
a	{a,b}	null
b	{a,b,c,d}	null
c	{b,c}	null
d	{b,d}	null
e	{e,f}	null
f	{e,f}	null