Recursive SQL Queries with PostgreSQL

Question

Scenario:

I have a application that receives one or more inputs and generate one or more outputs.

The application can use outputs from one or more executions as inputs in a new execution.

There are cases where a data is invalidated and all the generated output data from executions that used directly or indirectly this data, must be invalidated as well.

This is how I'm try to accomplish it:

CREATE TABLE execution (
 execution_id INT,
 data VARCHAR(36), 
 direction CHAR(1) NOT NULL CHECK (direction = 'I' OR direction = 'O'),
 PRIMARY KEY (execution_id, data));
INSERT INTO execution VALUES (1, 'aaaa', 'O');
INSERT INTO execution VALUES (1, 'bbbb', 'O');
INSERT INTO execution VALUES (2, 'aaaa', 'I');
INSERT INTO execution VALUES (2, 'bbbb', 'I');
INSERT INTO execution VALUES (2, 'cccc', 'O');
INSERT INTO execution VALUES (2, 'dddd', 'O');
INSERT INTO execution VALUES (3, 'aaaa', 'I');
INSERT INTO execution VALUES (3, 'cccc', 'I');
INSERT INTO execution VALUES (3, 'eeee', 'O');
INSERT INTO execution VALUES (4, 'bbbb', 'I');
INSERT INTO execution VALUES (4, 'ffff', 'O');

For this data, when I ask for the chain of execution that will be affceted by 'aaaa'data invalidation I expect to get:

• INSERT INTO execution VALUES (2, 'cccc', 'O');
• INSERT INTO execution VALUES (2, 'dddd', 'O');
• INSERT INTO execution VALUES (3, 'eeee', 'O');

Reason:

• execution 2 used 'aaaa' as input, so 'cccc' and 'dddd' should be invalidated too.
• execution 3 also used 'aaaa' as input and 'cccc' (that was just invalidade), so 'eeee' should be invalidated too.
• and so on ...

Query:

WITH RECURSIVE execution_chain AS (
 SELECT execution_id, data, direction, 1 AS level
 FROM execution 
 WHERE data = 'aaaa' AND direction = 'O'
 UNION ALL
 SELECT e.execution_id, e.data, e.direction, e.level + 1
 FROM execution e
 WHERE e.execution_id IN (
 SELECT e.execution_id
 FROM execution e
 JOIN execution_chain 
 ON e.data = execution_chain.data
 WHERE e.execution_id > execution_chain.execution_id
 )
 AND e.direction = 'O'
)
SELECT * FROM execution_chain;

I'm getting this error:

ERROR: recursive reference to query "execution_chain" must not appear within a subquery
LINE 11: JOIN execution_chain ec
 ^
SQL state: 42P19
Character: 339 

Any idea on how to "fix" this query will be really appreciated.

• Serg: Why (4, 'ffff', 'O') ? How 'ffff' depends on 'aaaa' ?
• VeryNiceArgumentException: Nice catch @Serg, my mistake. Already fixed it. Thanks

Answer 1

To simplify the query first compute execution dependencies to be followed

WITH RECURSIVE dep as (
 select i.data, i.execution_id idin, o.execution_id idout 
 from execution i
 join execution o on i.data = o.data and i.direction = 'I' and o.direction = 'O'
),
execution_chain AS (
 SELECT execution_id, data, direction, 1 AS level
 FROM execution 
 WHERE data = 'aaaa' and direction = 'O'
 
 UNION ALL
 
 SELECT e.execution_id, e.data, e.direction, ec.level + 1
 FROM execution_chain ec 
 JOIN dep ON dep.data = ec.data AND dep.idout = ec.execution_id
 JOIN execution e ON e.execution_id = dep.idin AND e.direction ='O'
)
SELECT distinct execution_id, data, direction
FROM execution_chain
WHERE level > 1
ORDER BY execution_id, data;

db<>fiddle

• VeryNiceArgumentException: you might be able to help me on this one too: stackoverflow.com/questions/71716563/… :)