gpt4 book ai didi

postgresql - Postgres 9.5 : Non-zero xmax of a row refenced as a foreign key

转载 作者:行者123 更新时间:2023-11-29 12:10:46 24 4
gpt4 key购买 nike

根据 http://www.postgresql.org/docs/current/static/ddl-system-columns.html :

xmax - The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting transaction hasn't committed yet, or that an attempted deletion was rolled back.

但是如果一行被另一行引用为外键,它也有一个非零的 xmax 值:

drop table if exists demo;
create table demo
(
id bigint primary key not null,
pid bigint,
constraint demo_pid_fk
foreign key (pid)
references demo (id)
);

insert into demo(id, pid) values(1, NULL);
insert into demo(id, pid) values(2, 1);
insert into demo(id, pid) values(3, 3);
select xmin, xmax, * from demo;
xmin | xmax | id | pid
------+------+----+-----
1074 | 1075 | 1 |
1075 | 0 | 2 | 1
1076 | 1076 | 3 | 3
(3 rows)

insert into demo(id, pid) values(4, 1);
select xmin, xmax, * from demo;
xmin | xmax | id | pid
------+------+----+-----
1074 | 1077 | 1 |
1075 | 0 | 2 | 1
1076 | 1076 | 3 | 3
1077 | 0 | 4 | 1
(4 rows)

在上面的例子中,似乎 xmax 被设置为引用该行作为外键的最后一个事务的 ID。为什么会这样? Postgres 如何知道该行仍然存在?

最佳答案

Bruce Momkian 在 MVCC, unveiling xmin and xmax work 上有一个演讲( slides ,搜索“使用 Xmax 的行锁”)。

长话短说:xmax 也用于行锁——文档中没有详细介绍,在这种特定情况下,xmax 用于确保其他事务远离从修改引用的元组(例如,CASCADE 将等待),除非 txid_current() 大于插入的 xmax。实际上,表中的那些非零 xmax 没有用,并被 VACUUM FULL demo 清除。

关于postgresql - Postgres 9.5 : Non-zero xmax of a row refenced as a foreign key,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36664518/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com