Recursive SQL Queries with PostgreSQL-CodePudding

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.

CodePudding user response：

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