主要观点：在 PostgreSQL 中，使用 CTE、DELETE 和 LIMIT 时，查询规划器可能会以意想不到的方式执行查询，导致删除的行数超过 LIMIT 1 的限制。

关键信息：

• 原始问题：如WITH deleted_tasks AS (DELETE FROM task_queue WHERE id IN (SELECT id FROM task_queue WHERE queue_group_id = 15 LIMIT 1 FOR UPDATE SKIP LOCKED) RETURNING item_id) SELECT * FROM deleted_tasks;可能会删除多个行。
• 原因：PostgreSQL 的查询规划器为优化整体查询执行，在某些情况下会使子查询在单个DELETE命令中多次执行，若子查询不保证每次返回相同行，就会导致删除更多行。

• 解决方法：

  • 添加ORDER BY可使子查询在无行跳过时选择行具有确定性，但与FOR UPDATE SKIP LOCKED结合仍可能导致删除超过LIMIT的行数。
  • 去除FOR UPDATE SKIP LOCKED但保留ORDER BY可使子查询完全幂等，正确删除指定行数，但规划器仍可能多次执行子查询。
  • 使用=操作符处理LIMIT 1可强制规划器仅评估子查询一次，避免非确定性和SKIP LOCKED的副作用。
  • 显式物化 CTE（PostgreSQL 12+）是涉及LIMIT（尤其是LIMIT > 1）时最可靠的方法，可确保子查询仅执行一次，保证正确性和可预测性。

重要细节：

• 不同查询方式的执行计划及行为表现，如各种情况下的loops值等。
• 强调了 CTE 中幂等性和确定性的重要性，以及EXPLAIN ANALYZE在发现问题行为中的作用。
• 提醒在使用 DML 直接在 CTE 中时要考虑是否是最清晰或最安全的方法。