postgresql - PostgreSQL Upsert使用系统列XMIN，XMAX和其他来区分插入和更新的行

免责声明:理论问题。
在这里，有人问了几个有关如何区分PostgreSQL upsert语句中插入和更新的行的问题。
这是一个简单的示例:

create table t(i int primary key, x int);
insert into t values(1,1);
insert into t values(1,11),(2,22)
    on conflict(i) do update set x = excluded.i*11
    returning *, xmin, xmax;

╔═══╤════╤══════╤══════╗
║ i │ x  │ xmin │ xmax ║
╠═══╪════╪══════╪══════╣
║ 1 │ 11 │ 7696 │ 7696 ║
║ 2 │ 22 │ 7696 │    0 ║
╚═══╧════╧══════╧══════╝

因此，xmax> 0(或xmax = xmin)-行已更新； xmax = 0-已插入行。
IMO不太清楚地解释了xmin和xmax列here的含义。
逻辑是否可以基于这些列？关于系统列(源代码除外)，是否还有更重要的解释？
最后，我对更新/插入行的猜测正确吗？

最佳答案

我认为这是一个有趣的问题，值得深入回答。请耐心一点，如果它有点长。
简而言之:您的猜测是正确的，您可以使用以下RETURNING子句来确定该行是否已插入且未更新:

RETURNING (xmax = 0) AS inserted

现在详细说明:
当更新一行时，PostgreSQL不会修改数据，而是创建该行的新版本。当不再需要旧版本时，将通过autovacuum删除。行的一个版本称为元组，因此在PostgreSQL中，每行可以有多个元组。xmax有两个不同的用途:

如文档中所述，它可以是删除(或更新)元组的事务的事务ID(“tuple”是“row”的另一个词)。只有交易ID在xmin和xmax之间的交易才能看到该元组。如果没有事务ID小于xmax的事务，则可以安全地删除旧的元组。

xmax也用于存储行锁。在PostgreSQL中，行锁不存储在锁表中，而是存储在元组中，以避免锁表溢出。
如果只有一个事务在该行上具有锁定，则xmax将包含锁定事务的事务ID。如果有多个事务在该行上具有锁，则xmax包含所谓的multixact的编号，multixact是一种数据结构，该数据结构又包含锁定事务的事务ID。

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是当前的元组ID，它由页码和页面中的元组号组成。由于表是新创建的，因此所有数据都在页面0中。
让我讨论heap_page_items返回的三行。

在行指针(lp)1处，我们找到了旧的，更新的元组。它最初具有ctid = (0,1)，但是在更新期间进行了修改，以包含当前版本的元组ID。元组由事务102507创建，并由事务102508(发布INSERT ... ON CONFLICT的事务)使之无效。该元组不再可见，在VACUUM期间将被删除。t_infomask显示xmin和xmax都属于已提交的事务，因此显示了何时创建和删除元组。 t_infomask2显示该元组已通过HOT(仅堆元组)更新进行了更新，这意味着更新后的元组与原始元组在同一页面中，并且没有修改索引列(请参见src/backend/access/heap/README.HOT)。

在行指针2处，我们看到由事务INSERT ... ON CONFLICT创建的新的，更新的元组(事务102508)。t_infomask显示此元组是更新的结果，xmin有效，并且xmax包含KEY SHARE行锁(由于事务已完成，因此不再相关)。该行锁定是在INSERT ... ON CONFLICT处理期间获取的。 t_infomask2显示这是一个HOT元组。

在第3行指针处，我们看到了新插入的行。t_infomask显示xmin有效，而xmax无效。 xmax设置为0，因为此值始终用于新插入的元组。

因此，更新后的行的非零xmax是由行锁引起的实现 Artifact 。可以想象有一天会重新实现INSERT ... ON CONFLICT，以便这种行为发生变化，但是我认为这不太可能。

关于postgresql - PostgreSQL Upsert使用系统列XMIN，XMAX和其他来区分插入和更新的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39058213/