Chained CTEs not executing in order? PostgreSQL

Question

I have the following function:

CREATE OR REPLACE FUNCTION fn_cleanup_proxies(cleanup_range integer, proxies json)
RETURNS udt_cleanup_count AS $func$
DECLARE result_count udt_cleanup_count;
BEGIN
    WITH input_proxies AS (SELECT * FROM json_populate_recordset(null::udt_proxy, proxies)),
    old_proxies AS (SELECT p.id, p.address, p.isp_id FROM proxy AS p INNER JOIN input_proxies AS i ON p.address = i.address AND p.port = i.port AND p.type_id = i.type_id),
    old_proxies_city AS (SELECT address FROM old_proxies WHERE address NOT IN (SELECT address FROM proxy WHERE modified_date > NOW() - INTERVAL '1 days' * cleanup_range)),
    old_proxies_isp AS (SELECT isp_id FROM old_proxies WHERE isp_id NOT IN (SELECT isp_id FROM proxy WHERE modified_date > NOW() - INTERVAL '1 days' * cleanup_range)),
    deleted_proxy AS (DELETE FROM proxy WHERE id IN (SELECT id FROM old_proxies) RETURNING *),
    deleted_isp AS (DELETE FROM isp WHERE id IN (SELECT * FROM old_proxies_isp) RETURNING *),
    deleted_city AS (DELETE FROM city WHERE proxy_address IN (SELECT * FROM old_proxies_city) RETURNING *)
    
    SELECT (SELECT count(*) FROM deleted_proxy), (SELECT count(*) FROM deleted_city), (SELECT count(*) FROM deleted_isp)
    INTO result_count.proxy_count, result_count.city_count, result_count.isp_count;
    
    RETURN result_count;
END
$func$  LANGUAGE plpgsql;

When I execute it I get the following error:

ERROR:  update or delete on table "isp" violates foreign key constraint "proxy_isp_id_fkey" on table "proxy"
DETAIL:  Key (id)=(23969) is still referenced from table "proxy".

I am getting the impression that the chained CTEs are not executed in sequence but rather in parallel. This is because when I perform the following three delete statements in order manually one by one:

deleted_proxy AS (DELETE FROM proxy WHERE id IN (SELECT id FROM old_proxies) RETURNING *),
deleted_isp AS (DELETE FROM isp WHERE id IN (SELECT * FROM old_proxies_isp) RETURNING *),
deleted_city AS (DELETE FROM city WHERE proxy_address IN (SELECT * FROM old_proxies_city) RETURNING *)

I am not getting any constraint issues and everything seems to work just fine.

Also as you might have noticed from my query, I'm a bit new to PostgreSQL so if there are any suggestions to improve upon my query, please feel free to comment.

user_330315 · Answer

I am getting the impression that the chained CTEs are not executed in sequence but rather in parallel

You are right. Quote from the manual

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable

As you don't actually use the results of the RETURNING * clauses for the DELETEs, why do you use a CTE anyway? Just run all the statements one after the other.

user_13808319 · Answer

You can force a dependency from deleted_isp to deleted_proxy as follows:

deleted_isp AS (
      DELETE FROM isp 
       WHERE id IN (SELECT * FROM old_proxies_isp) 
         AND proxy_id IN (SELECT id FROM deleted_proxy)
      RETURNING *

If city has an FK into either proxy or isp, then add something similar.

Chained CTEs not executing in order? PostgreSQL

2 Answers