含FROM与ctid的PostgreSQL UPDATE语句执行失败问题咨询

我有如下简化形式的UPDATE语句：

UPDATE changes.table1 a
   SET checked_out_at = a.checked_out_at
  FROM unified.view2 b
 WHERE a.ctid = '(4,48)'
   AND b.some_pkey = a.some_pkey
RETURNING *;

执行该语句后显示UPDATE 0，没有行被更新。但移除关联表后，语句可正常执行（因ctid会变更，仅能执行一次，后续需查找新ctid）：

UPDATE changes.table1 a
   SET checked_out_at = a.checked_out_at
 WHERE a.ctid = '(4,48)'
RETURNING *;

遗憾的是，我需要通过关联表获取RETURNING子句中的部分数据。请问：

• 为何这两个特性无法兼容？
• 关联行的ctid是否与单表行不同？
• 能否实现类似需求？

为什么关联后返回UPDATE 0？ #

问题出在FROM子句的关联逻辑上。PostgreSQL中带FROM的UPDATE语句，会先将目标表changes.table1和关联表unified.view2做笛卡尔积，再筛选符合条件的行执行更新。

当你用a.ctid = '(4,48)'定位目标行时，如果unified.view2中没有与该行匹配的记录（即b.some_pkey = a.some_pkey无匹配结果），整个关联结果集为空，自然不会有任何行被更新，返回UPDATE 0。

而单表UPDATE时，只要ctid对应的行存在，就会执行更新（哪怕SET的是原值，PostgreSQL也会标记该行被修改，导致ctid变更）。

关联行的ctid和单表行的ctid是一回事吗？ #

是的，ctid是PostgreSQL中表行的物理位置标识符，仅属于目标表changes.table1的行。关联表unified.view2的行有自己的ctid，但你语句中引用的a.ctid始终指向table1中该行的物理位置，和关联表无关。

你遇到的问题和ctid本身无关，核心是关联条件是否匹配到数据。

如何实现“通过关联表获取RETURNING数据”的需求？ #

有两种可行方案：

方案1：确保关联条件匹配，或改用LEFT JOIN #

如果unified.view2中应该存在匹配的行，先检查some_pkey的值是否正确，确认关联条件能匹配到数据。如果允许关联表无匹配时仍更新目标行，可以把FROM改成LEFT JOIN写法：

UPDATE changes.table1 a
   SET checked_out_at = a.checked_out_at
  LEFT JOIN unified.view2 b ON b.some_pkey = a.some_pkey
 WHERE a.ctid = '(4,48)'
RETURNING a.*, b.some_column; -- 按需返回关联表的字段

注意：用LEFT JOIN时，即使b表无匹配，a表的行仍会被更新，此时b的字段会返回NULL。

方案2：拆分查询，先取关联数据再执行UPDATE #

如果关联表可能无匹配，但你需要明确获取关联数据，可以分两步操作：

1. 先查询目标行的关联数据：

SELECT b.*
  FROM changes.table1 a
  JOIN unified.view2 b ON b.some_pkey = a.some_pkey
 WHERE a.ctid = '(4,48)';

2. 执行单表UPDATE：

UPDATE changes.table1 a
   SET checked_out_at = a.checked_out_at
 WHERE a.ctid = '(4,48)'
RETURNING *;

最后把两个查询的结果合并即可。这种方式更直观，也能避免关联无匹配导致UPDATE失败的问题。

额外提醒：ctid的局限性 #

ctid是行的物理位置，当表发生VACUUM、表重写、行移动时会变更，不能作为长期的行标识符。如果后续需要重复定位行，建议用表的主键或唯一约束，而不是ctid。

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