时间戳

首页 > 解决方案 > 链式 CTE 未按顺序执行？PostgreSQL

sql - 链式 CTE 未按顺序执行？PostgreSQL

问题描述

我有以下功能：

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;

当我执行它时，我收到以下错误：

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".

我得到的印象是链接的 CTE 不是按顺序执行，而是并行执行。这是因为当我手动依次执行以下三个删除语句时：

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 *)

我没有遇到任何约束问题，一切似乎都很好。

另外，正如您可能从我的查询中注意到的那样，我对 PostgreSQL 有点陌生，所以如果有任何改进我的查询的建议，请随时发表评论。

标签： sqlpostgresql

解决方案

我的印象是链接的 CTE 不是按顺序执行，而是并行执行

你说的对。从手册中引用

WITH 中的子语句彼此同时执行，并与主查询同时执行。因此，当在 WITH 中使用数据修改语句时，指定更新实际发生的顺序是不可预测的

由于您实际上并未将RETURNING *子句的结果用于 DELETE，为什么还要使用 CTE？只需一个接一个地运行所有语句。

推荐阅读