PostgreSQL WITH子句中DELETE未生效导致文件无法删除的问题求助

我有两张用于处理附件的表：

• files表：包含id及其他信息
• attachments表：包含id_file（关联files表的id作为外键）及附件的其他信息

设计两张表是为了在服务器上共享文件而不重复存储。当删除附件时，我希望仅在没有其他附件引用该文件id的情况下，才删除对应的文件。

为此我编写了如下SQL语句，用于删除关联到TABLE_NAME表第26条数据的所有附件：

WITH deleted_files AS (
  DELETE FROM public.attachments a
  WHERE a.id_in_table = 26
    AND a.table_name = 'TABLE_NAME'
  RETURNING id_file
)
DELETE FROM public.files f
WHERE f.id IN (SELECT id FROM deleted_files)
  AND NOT EXISTS (
    SELECT 1
    FROM public.attachments a
    WHERE a.id_file = f.id
);

遇到的问题：

• 附件能被正确删除，但文件并未被删除
• 再次执行相同语句无任何删除操作（此为正常现象）
• 若单独执行第二个DELETE语句并传入第一个DELETE返回的id，文件能被正确删除

请问是否存在某种机制保留了已删除记录的副本，导致无法删除文件？

问题出在PostgreSQL的事务快照隔离机制上。在同一个CTE（WITH子句）的事务中，所有语句共享同一个快照——也就是事务启动时的数据库状态。

当你在deleted_files CTE里删除了attachments的记录后，后续的DELETE FROM files语句查询attachments表时，看到的仍然是事务开始前的状态（包含那些刚被删除的附件记录）。所以NOT EXISTS条件会判定该文件还有其他附件引用，最终不会执行文件删除。

简单说：同一个事务里，删除操作的变更不会被同事务内的后续查询看到，导致NOT EXISTS判断错误。

方法1：分两次独立事务执行 #

先执行附件删除并记录返回的id_file，再单独执行文件删除语句。这也是你测试过可行的方式，逻辑直观，适合手动或脚本分步操作。

方法2：修改CTE逻辑规避快照问题 #

先在事务启动前获取要删除的附件列表，删除附件后再统计剩余引用数，最终删除无引用的文件：

WITH attachments_to_delete AS (
  SELECT id_file, id
  FROM public.attachments
  WHERE id_in_table = 26
    AND table_name = 'TABLE_NAME'
), deleted_attachments AS (
  DELETE FROM public.attachments
  WHERE id IN (SELECT id FROM attachments_to_delete)
)
DELETE FROM public.files f
WHERE f.id IN (SELECT id_file FROM attachments_to_delete)
  AND (
    SELECT COUNT(*)
    FROM public.attachments a
    WHERE a.id_file = f.id
  ) = 0;

如果要优化性能，也可以用LEFT JOIN批量统计剩余引用：

WITH attachments_to_delete AS (
  SELECT id_file, id
  FROM public.attachments
  WHERE id_in_table = 26
    AND table_name = 'TABLE_NAME'
), deleted_attachments AS (
  DELETE FROM public.attachments
  WHERE id IN (SELECT id FROM attachments_to_delete)
), remaining_references AS (
  SELECT a.id_file, COUNT(*) AS ref_count
  FROM public.attachments a
  JOIN attachments_to_delete t ON a.id_file = t.id_file
  GROUP BY a.id_file
)
DELETE FROM public.files f
USING remaining_references r
WHERE f.id = r.id_file
  AND r.ref_count = 0;

方法3：用触发器实现自动清理 #

如果希望每次删除附件时自动处理无引用文件，可以给attachments表创建AFTER DELETE触发器：

CREATE OR REPLACE FUNCTION cleanup_unused_files()
RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM public.files f
  WHERE f.id = OLD.id_file
    AND NOT EXISTS (
      SELECT 1 FROM public.attachments a WHERE a.id_file = OLD.id_file
    );
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_cleanup_files
AFTER DELETE ON public.attachments
FOR EACH ROW
EXECUTE FUNCTION cleanup_unused_files();

此后每次删除附件，触发器会自动检查对应文件的剩余引用，无引用则直接删除文件。注意：批量删除附件时触发器会逐行执行，需根据数据量评估性能影响。

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