PostgreSQL Upsert 使用系统列 XMIN、XMAX 等来区分插入和更新的行

我认为这是一个值得深入回答的有趣问题；如果有点长，请耐心听我说.

简而言之:您的猜测是正确的，您可以使用以下RETURNING子句来确定行是否已插入且未更新:

RETURNING (xmax = 0) AS inserted

现在详细解释如下:

当一行被更新时，PostgreSQL不会修改数据，而是创建该行的一个new version；当不再需要旧版本时，它将被autovacuum删除.行的一个版本称为tuple，因此在PostgreSQL中，每行可以有多个元组.

xmax有两个不同的用途:

1. 如文档中所述，它可以是删除(或更新)元组的事务的事务ID("元组"是"行"的另一个词).只有事务ID在xmin到xmax之间的事务才能看到元组.如果没有事务ID小于xmax的事务，则可以安全地删除旧元组.

2. xmax is also used to store row locks. In PostgreSQL, row locks are not stored in the lock table, but in the tuple to avoid overflow of the lock table.
   If only one transaction has a lock on the row, xmax will contain the transaction ID of the locking transaction. If more than one transaction has a lock on the row, xmax contains the number of a so-called multixact, which is a data structure that in turn contains the transaction IDs of the locking transactions.

xmax的文档并不完整，因为这个字段的确切含义被认为是一个实现细节，如果不知道元组的t_infomask，就无法理解它，而元组不能通过SQL立即看到.

您可以安装contrib模块pageinspect来查看元组的此字段和其他字段.

我运行了您的示例，这是我使用heap_page_items函数判断细节时看到的(在我的情况下，交易ID号当然不同):

SELECT *, ctid, xmin, xmax FROM t;

┌───┬────┬───────┬────────┬────────┐
│ i │ x  │ ctid  │  xmin  │  xmax  │
├───┼────┼───────┼────────┼────────┤
│ 1 │ 11 │ (0,2) │ 102508 │ 102508 │
│ 2 │ 22 │ (0,3) │ 102508 │      0 │
└───┴────┴───────┴────────┴────────┘
(2 rows)

SELECT lp, lp_off, t_xmin, t_xmax, t_ctid,
       to_hex(t_infomask) AS t_infomask, to_hex(t_infomask2) AS t_infomask2
FROM heap_page_items(get_raw_page('laurenz.t', 0));

┌────┬────────┬────────┬────────┬────────┬────────────┬─────────────┐
│ lp │ lp_off │ t_xmin │ t_xmax │ t_ctid │ t_infomask │ t_infomask2 │
├────┼────────┼────────┼────────┼────────┼────────────┼─────────────┤
│  1 │   8160 │ 102507 │ 102508 │ (0,2)  │ 500        │ 4002        │
│  2 │   8128 │ 102508 │ 102508 │ (0,2)  │ 2190       │ 8002        │
│  3 │   8096 │ 102508 │      0 │ (0,3)  │ 900        │ 2           │
└────┴────────┴────────┴────────┴────────┴────────────┴─────────────┘
(3 rows)

t_infomask和t_infomask2的含义可以在src/include/access/htup_details.h中找到.lp_off是页面中的元组数据的偏移量，t_ctid是由页面编号和页面内的元组编号组成的current tuple ID.由于该表是新创建的，所以所有数据都在第0页.

让我讨论一下heap_page_items返回的三行.

1. 在line pointer(lp)1处，我们找到了旧的、更新的元组.它最初有ctid = (0,1)个，但在更新过程中被修改为包含当前版本的元组ID.该元组由事务INSERT ... ON CONFLICT507创建，并由事务INSERT ... ON CONFLICT508(发出INSERT ... ON CONFLICT的事务)失效.这个元组不再可见，将在VACUUM期间删除.

   t_infomask显示xmin和xmax都属于提交的事务，因此显示元组的创建和删除时间.t_infomask2显示元组更新为热(heap only tuple)更新，这意味着更新后的元组与原始元组位于同一页面，并且没有修改索引列(参见src/backend/access/heap/README.HOT).

2. 在第2行，我们看到了事务INSERT ... ON CONFLICT(事务102508)创建的新的、更新的元组.

   t_infomask表示该元组是更新的结果，xmin表示有效，xmax表示包含KEY SHARE行锁(自事务完成以来，该锁不再相关).该行锁是在INSERT ... ON CONFLICT处理期间获取的.t_infomask2表明这是一个热元组.

3. 在第3行，我们看到新插入的行.

   t_infomask表示xmin有效，xmax无效.xmax设置为0，因为该值始终用于新插入的元组.

因此，更新行的非零xmax是由行锁引起的实现工件.可以想象，INSERT ... ON CONFLICT有一天会重新实施，从而改变这种行为，但我认为这不太可能.