PostgreSQL中的UPDATE与DELETE+INSERT相同吗？

飞象数据 2022-12-31

847

介绍

我们知道PostgreSQL 不会就地更新表行(https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY)。相反，它写入行的新版本（行版本的 PostgreSQL 术语是“元组”），并保留旧的行版本以处理并发读取请求。VACUUM
稍后删除这些“死元组”。

如果删除一行并插入新行，效果是类似的：我们有一个死元组和一个新的活元组。这就是为什么许多人（包括我在内）向初学者解释“PostgreSQL 中的 UPDATE
第一步差不多与 DELETE
相同，紧跟着第二步就是INSERT
”。

这篇文章就是关于那个“差不多”的。

显示UPDATE和DELETE+INSERT之间差异的示例

让我们以这个简单的测试表为例：

CREATE TABLE uptest (
   id smallint PRIMARY KEY,
   val smallint NOT NULL
);
 
INSERT INTO uptest VALUES (1, 42);

在以下两个测试中，我们将从两个并发会话中发出语句。

首先，UPDATE
：

Session 1                     Session 2
 
BEGIN;
 
UPDATE uptest SET id = 2
   WHERE val = 42;
                               SELECT id FROM uptest
                                  WHERE val = 42
                                  FOR UPDATE;  -- hangs
COMMIT;
                               -- one row is returned

让我们在第二次测试之前重置表；

TRUNCATE uptest;
INSERT INTO uptest VALUES (1, 42);

现在让我们用DELETE
和重复实验INSERT
：

Session 1                     Session 2
 
BEGIN;
 
DELETE FROM uptest
   WHERE id = 1;
 
INSERT INTO uptest VALUES (2, 42);
                               SELECT id FROM uptest
                                  WHERE val = 42
                                  FOR UPDATE;  -- hangs
COMMIT;
                               -- no row is returned

对观察到的差异的解释

该文档(https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED)描述了当 SQL 语句在具有默认READ COMMITTED
隔离级别的事务中遇到锁时会发生什么：

UPDATE
、DELETE
、SELECT FOR UPDATE
和SELECT FOR SHARE
命令的行为与SELECT
在搜索目标行方面：他们只会找到在命令开始时提交的目标行。但是，这样的目标行在找到时可能已经被另一个并发事务更新（或删除或锁定）。在这种情况下，潜在的更新者将等待第一个更新事务提交或回滚（如果它仍在进行中）。如果第一个更新程序回滚，那么它的效果就无效了，第二个更新程序可以继续更新最初找到的行。如果第一个更新程序提交，第二个更新程序将在第一个更新程序删除该行时忽略该行，否则它将尝试将其操作应用于该行的更新版本。命令的搜索条件（WHERE
子句）被重新评估以查看该行的更新版本是否仍然匹配搜索条件。如果是，则第二个更新程序使用该行的更新版本继续其操作。在SELECT FOR UPDATE
和SELECT FOR SHARE
的情况下，这意味着它是被锁定并返回给客户端的行的更新版本。

上面显示了 PostgreSQL 有一些方法可以找到更新行的新版本。这就是第一个实验返回结果行的原因。在第二个实验中，旧的、删除的行和新插入的行之间没有联系，这就是为什么在这种情况下我们没有得到结果。

要弄清楚新、旧版本是如何连接的，我们必须更深入地研究。

使用“pageinspect”查看UPDATE

该扩展pageinspect
允许我们查看 PostgreSQL 数据页面中的所有数据。它需要超级用户权限。

让我们用它来看看第一个实验后磁盘上有什么：

TRUNCATE uptest;
INSERT INTO uptest VALUES (1, 42);
 
UPDATE uptest SET id = 2 WHERE val = 42;
 
SELECT lp,
       t_xmin AS xmin,
       t_xmax AS xmax,
       t_ctid,
       to_hex(t_infomask2) AS infomask2,
       to_hex(t_infomask) AS infomask,
       t_attrs       
FROM heap_page_item_attrs(get_raw_page('uptest', 0), 'uptest');
 
 lp |  xmin  |  xmax  | t_ctid | infomask2 | infomask |        t_attrs        
----+--------+--------+--------+-----------+----------+-----------------------
  1 | 385688 | 385689 | (0,2)  | 2002      | 100      | {"\\x0100","\\x2a00"}
  2 | 385689 |      0 | (0,2)  | 2         | 2800     | {"\\x0200","\\x2a00"}
(2 rows)

第一个条目是该行的旧版本，第二个条目是新版本。

lp
是行指针号，它表示所述数据页内的元组的号。这与页码一起构成了元组的物理地址（元组 ID或tid
）。

因此，t_ctid
存储在元组头中的通常是多余的，因为它隐含在行指针中。但是，它在UPDATE
: 然后t_ctid
包含行的更新版本的元组标识符变得相关。

这是旧行版本和更新版本之间的“缺失环节”！

使用“pageinspect”查看DELETE+INSERT

让我们将其与DELETE
+INSERT
进行比较：

TRUNCATE uptest;
INSERT INTO uptest VALUES (1, 42);
 
BEGIN;
DELETE FROM uptest WHERE id = 1;
INSERT INTO uptest VALUES (2, 42);
COMMIT;
 
SELECT lp,
       t_xmin AS xmin,
       t_xmax AS xmax,
       t_ctid,
       to_hex(t_infomask2) AS infomask2,
       to_hex(t_infomask) AS infomask,
       t_attrs       
FROM heap_page_item_attrs(get_raw_page('uptest', 0), 'uptest');
 
 lp |  xmin  |  xmax  | t_ctid | infomask2 | infomask |        t_attrs        
----+--------+--------+--------+-----------+----------+-----------------------
  1 | 385691 | 385692 | (0,1)  | 2002      | 100      | {"\\x0100","\\x2a00"}
  2 | 385692 |      0 | (0,2)  | 2         | 800      | {"\\x0200","\\x2a00"}
(2 rows)

这里来自旧的、删除的元组的t_ctid
列没有改变，并且没有指向新元组的链接。第二个元组未被SELECT ... FOR UPDATE
，因为它对用于扫描表的“快照”“不可见”。

infomask和的区别infomask2

属性infomask
和也存在一些相关差异infomask2
。您可以在 PostgreSQL 源文件中看到这些标志的含义src/include/access/htup_details.h
。

对于旧元组，两种情况下的值相同：

infomask2
: 2为列数，HEAP_KEYS_UPDATED
(0x2000)表示删除或更新元组
infomask
: HEAP_XMIN_COMMITTED
(0x0100) 表示元组在被删除之前是有效的（提示位）

对于新元组，有一些区别：

infomask
: 两个case都有HEAP_XMAX_INVALID
(0x0800)set(没有被删除)，但是UPDATE
case也有HEAP_UPDATED
(0x2000)，说明这是一个UPDATE

结论

为了理解UPDATE
和DELETE
+INSERT
之间的区别，我们仔细查看了元组标题。我们看到infomask
,infomask2
和t_ctid
，其中后者提供了新旧版本行之间的链接。

PostgreSQL 的行头占用 23 个字节，这比其他数据库中的存储开销更大，但是 PostgreSQL 特殊的多版本和元组可见性实现需要它。

UPDATE
在 PostgreSQL 中可能具有挑战性：如果您想了解更多有关其问题以及如何处理这些问题的信息，请阅读我关于HOT update 的文章。

文章转载自飞象数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。