使用系统列区分UPSERT中插入和更新的行

Question

使用系统列区分UPSERT中插入和更新的行

55

最近在SO上有几个问题与如何区分PostgreSQL UPSERT语句(INSERT ... ON CONFLICT ... DO UPDATE ...)中插入和更新的行有关。以下是一个简单的例子：

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 → 行已插入

在我看来，从手册中的解释中并不太清楚。

是否可以基于这些列构建逻辑？除了源代码之外，还有更重要的系统列解释吗？

我的假设是正确的吗？

- Abelisto

为什么不在更新期间创建另一列用于元数据设置？ - vol7ron

1

@vol7ron 因为它正在减慢整个查询的速度。我觉得现有的列（包括系统列）已经足够了。 - Abelisto

有趣。我的直觉说它应该能工作，但这是未记录的行为，并且不能保证将来不会更改。我不太愿意在专业项目中使用它。 - klin

@klin 我的直觉和你的直觉一致。对于此类查询，这实际上是一个很明显的任务。考虑到 PostgreSQL 的开发速度... - Abelisto

5

说服Postgres开发人员将此问题正式化（记录）可能是值得的。 - klin

1

是的，我不建议依赖旧元组的xmax，尽管它可以在当前实现中工作。我认为我们应该有一个关键字或伪函数来请求插入与更新决策。请在pgsql-hackers上发布以指出这一点。 - Craig Ringer

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Laurenz Albe · Accepted Answer

我认为这是一个值得深入回答的有趣问题, 如果答案有点长请耐心等待。

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

RETURNING (xmax = 0) AS inserted

现在进行详细解释：

当一行被更新时，PostgreSQL 不会修改数据，而是创建一个新版本的行；旧版本将在不再需要时被自动清除。行的一个版本称为元组，因此在 PostgreSQL 中，一行可以有多个元组。

xmax 有两个不同的用途：

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

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

关于 xmax 的文档不完整，因为这个字段的确切含义被认为是实现细节，如果不知道元组的 t_infomask，就无法理解它，而 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是由行锁引起的实现工件。可以想象有一天会重新实现INSERT ... ON CONFLICT以更改此行为，但我认为这是不太可能的。