PostgreSQL使用CTE先删后插时新插入行被误删的问题

需实现操作：删除fdw_ca_millingdb.job_detail表中job_id=214的所有行，再插入该job_id的新记录。

异常现象：执行完整的CTE语句时，刚插入的行被立即删除；单独执行查询部分（注释删除和插入语句）能得到正确的待插入数据。

用户提供的SQL代码：

WITH cte_delete AS(DELETE FROM fdw_ca_millingdb.job_detail WHERE job_id = 214),
--WITH 
newRows AS (
WITH abcT AS 
(SELECT fdw_ca_millingdb.item_abc_map.item_id AS item_id, SUM(ordered_quantity) AS ordered_quantity FROM work_order_dadsfetail
LEFT JOIN fdw_ca_millingdb.item_abc_map ON fdw_ca_millingdb.item_abc_map.abc_item_id = work_order_detail.item_id
WHERE work_order_detail.item_id ILIKE ANY (array['1%', '2%', '3%', '4%']) AND work_order_detail.id = 254919
GROUP BY fdw_ca_millingdb.item_abc_map.item_id),

logT AS
(SELECT fdw_ca_millingdb.batcher_log.item_id batcher_log_item_id, SUM(fdw_ca_millingdb.batcher_log.ending_weight - fdw_ca_millingdb.batcher_log.starting_weight) AS actual_weight FROM fdw_ca_millingdb.job
LEFT JOIN fdw_ca_millingdb.batcher_log ON fdw_ca_millingdb.batcher_log.job_id = fdw_ca_millingdb.job.id
WHERE fdw_ca_millingdb.job.work_order_id = 254919
GROUP BY fdw_ca_millingdb.batcher_log.item_id)

SELECT 214 AS job_id, ROW_NUMBER() OVER(ORDER BY abcT.item_id) - 1 AS line_num, ordered_quantity - actual_weight AS ordered_quantity,
abcT.item_id AS item_id, fdw_ca_millingdb.item.description AS item_description, bin.id AS bin_id FROM abcT
FULL JOIN logT ON logT.batcher_log_item_id = abcT.item_id
LEFT JOIN fdw_ca_millingdb.item ON fdw_ca_millingdb.item.id = abcT.item_id
LEFT JOIN fdw_ca_millingdb.bin ON fdw_ca_millingdb.bin.item_id = abcT.item_id WHERE bin.mode = 1)

INSERT INTO fdw_ca_millingdb.job_detail (job_id, line_num, ordered_quantity, item_id, item_description, bin_id)
SELECT job_id, line_num, ordered_quantity, item_id, item_description, bin_id FROM newRows;

可能的原因 #

1. CTE并行执行顺序问题：PostgreSQL中CTE默认并行执行，cte_delete和newRows可能同时触发，导致插入的行被后续删除操作误删。
2. 目标表存在触发器：fdw_ca_millingdb.job_detail表可能配置了触发器（如AFTER INSERT触发DELETE的规则），插入后数据被自动删除。
3. FDW外部表特性：该表是外部表，外部数据源可能存在同步机制、约束或清理规则，导致插入的数据被回滚或删除。
4. 事务未提交：若存在隐式事务或手动开启事务未提交，会导致数据未持久化。

解决方案 #

方案1：拆分语句并用事务包裹 #

将删除和插入拆分为独立语句，放在同一事务中，确保先完成删除再执行插入：

BEGIN;
-- 执行删除操作
DELETE FROM fdw_ca_millingdb.job_detail WHERE job_id = 214;

-- 执行插入操作
WITH abcT AS 
(SELECT fdw_ca_millingdb.item_abc_map.item_id AS item_id, SUM(ordered_quantity) AS ordered_quantity FROM work_order_detail
LEFT JOIN fdw_ca_millingdb.item_abc_map ON fdw_ca_millingdb.item_abc_map.abc_item_id = work_order_detail.item_id
WHERE work_order_detail.item_id ILIKE ANY (array['1%', '2%', '3%', '4%']) AND work_order_detail.id = 254919
GROUP BY fdw_ca_millingdb.item_abc_map.item_id),
logT AS
(SELECT fdw_ca_millingdb.batcher_log.item_id batcher_log_item_id, SUM(fdw_ca_millingdb.batcher_log.ending_weight - fdw_ca_millingdb.batcher_log.starting_weight) AS actual_weight FROM fdw_ca_millingdb.job
LEFT JOIN fdw_ca_millingdb.batcher_log ON fdw_ca_millingdb.batcher_log.job_id = fdw_ca_millingdb.job.id
WHERE fdw_ca_millingdb.job.work_order_id = 254919
GROUP BY fdw_ca_millingdb.batcher_log.item_id)
INSERT INTO fdw_ca_millingdb.job_detail (job_id, line_num, ordered_quantity, item_id, item_description, bin_id)
SELECT 214 AS job_id, ROW_NUMBER() OVER(ORDER BY abcT.item_id) - 1 AS line_num, ordered_quantity - actual_weight AS ordered_quantity,
abcT.item_id AS item_id, fdw_ca_millingdb.item.description AS item_description, bin.id AS bin_id FROM abcT
FULL JOIN logT ON logT.batcher_log_item_id = abcT.item_id
LEFT JOIN fdw_ca_millingdb.item ON fdw_ca_millingdb.item.id = abcT.item_id
LEFT JOIN fdw_ca_millingdb.bin ON fdw_ca_millingdb.bin.item_id = abcT.item_id WHERE bin.mode = 1;

-- 提交事务
COMMIT;

方案2：检查目标表的触发器 #

执行以下语句查看job_detail表是否存在异常触发器：

SELECT tgname, pg_get_triggerdef(t.oid) AS trigger_def
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'job_detail' 
  AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'fdw_ca_millingdb');

若发现插入后自动删除的触发器，需调整逻辑或临时禁用。

方案3：排查FDW外部数据源 #

确认外部数据源（如远程数据库）是否存在同步规则、数据清理任务或约束，导致插入数据被删除。

方案4：修改CTE强制执行顺序 #

通过让newRows依赖cte_delete的结果，强制PostgreSQL先执行删除再生成插入数据：

WITH cte_delete AS(DELETE FROM fdw_ca_millingdb.job_detail WHERE job_id = 214 RETURNING *),
newRows AS (
WITH abcT AS 
(SELECT fdw_ca_millingdb.item_abc_map.item_id AS item_id, SUM(ordered_quantity) AS ordered_quantity FROM work_order_detail
LEFT JOIN fdw_ca_millingdb.item_abc_map ON fdw_ca_millingdb.item_abc_map.abc_item_id = work_order_detail.item_id
WHERE work_order_detail.item_id ILIKE ANY (array['1%', '2%', '3%', '4%']) AND work_order_detail.id = 254919
GROUP BY fdw_ca_millingdb.item_abc_map.item_id),
logT AS
(SELECT fdw_ca_millingdb.batcher_log.item_id batcher_log_item_id, SUM(fdw_ca_millingdb.batcher_log.ending_weight - fdw_ca_millingdb.batcher_log.starting_weight) AS actual_weight FROM fdw_ca_millingdb.job
LEFT JOIN fdw_ca_millingdb.batcher_log ON fdw_ca_millingdb.batcher_log.job_id = fdw_ca_millingdb.job.id
WHERE fdw_ca_millingdb.job.work_order_id = 254919
GROUP BY fdw_ca_millingdb.batcher_log.item_id)
SELECT 214 AS job_id, ROW_NUMBER() OVER(ORDER BY abcT.item_id) - 1 AS line_num, ordered_quantity - actual_weight AS ordered_quantity,
abcT.item_id AS item_id, fdw_ca_millingdb.item.description AS item_description, bin.id AS bin_id FROM abcT
FULL JOIN logT ON logT.batcher_log_item_id = abcT.item_id
LEFT JOIN fdw_ca_millingdb.item ON fdw_ca_millingdb.item.id = abcT.item_id
LEFT JOIN fdw_ca_millingdb.bin ON fdw_ca_millingdb.bin.item_id = abcT.item_id 
CROSS JOIN (SELECT 1 FROM cte_delete LIMIT 0) dummy -- 强制依赖删除操作的执行
WHERE bin.mode = 1)
INSERT INTO fdw_ca_millingdb.job_detail (job_id, line_num, ordered_quantity, item_id, item_description, bin_id)
SELECT job_id, line_num, ordered_quantity, item_id, item_description, bin_id FROM newRows;

内容的提问来源于stack exchange，提问作者cxp cxd