pg更新表元组(数据行)时不会直接修改该元组,它会写入新版本元组并保留旧版本元组,这样来提高并发读取的请求,旧版本的"死元组"稍后由vacuum来清理。
因而如果我们删除一行数据并且插入一行新数据,数据库里是有一个死元组和一个新的活元组。所以我们通常会这样向别人解释pg里的"update":
update ≈ delete insert
下面我们通过实验来理解这个"约等于"的差异。
创建测试表并插入一条数据:
create table test_update (
id int2 primary key,
val int2 not null
);
insert into test_update values (1, 100);
接下来的两个测试,我们使用两个并发会话发起语句,如下图。
重置表后开始第二次测试
truncate test_update;
insert into test_update values (1, 100);
第二次测试我们使用delete和insert操作来进行测试,如下图
对上面两个测试结果差异的解释可以参考官方文档
update, delete, select for update, and select for share commands behave the same as select in terms of searching for target rows: they will only find target rows that were committed as of the command start time. however, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. in this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). if the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. if the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. the search condition of the command (the where clause) is re-evaluated to see if the updated version of the row still matches the search condition. if so, the second updater proceeds with its operation using the updated version of the row. in the case of select for update and select for share, this means it is the updated version of the row that is locked and returned to the client.
上面解释了第一个测试返回一行数据的原因,而在第二个测试中,删除的行和新插入的行之间没有联系(第一个测试里通过pageinspect可以观察ctid值),所以第二种情况下并没有得到结果。
参考文章:
https://www.cybertec-postgresql.com/en/is-update-the-same-as-delete-insert-in-postgresql/