questions about snapshot building logic

From: Вадим Самохин <samokhinvadim(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: questions about snapshot building logic
Date: 2022-08-29 11:29:33
Message-ID: CAGVmuwomSpZcB-=fufk=c-OEUZM5qOURv=VpZ1JYanVz1V8Ruw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've read some posts about how snapshots are built (here
<https://www.interdb.jp/pg/pgsql05.html>, here
<https://brandur.org/postgres-atomicity>, and here
<https://postgrespro.com/blog/pgsql/5967899>) and I thought that I
understood what's going on -- until I tried that in practice. My postgres
version is 11.6.

So, I start a first session:
*session 1*:
begin isolation level repeatable read;
update restaurant set address = '1' where id = 1;
select txid_current(); -- it is 1402
select txid_current_snapshot(); -- 1402:1402:

*Question 1*: why is xmax equal to xmin? Isn't xmax the id of a
not-yet-started transaction, that is 1402 + 1 = 1403?

after that, I start a second session:

*session 2*:
begin isolation level repeatable read;
update restaurant set address = '2' where id = 2;
select txid_current(); -- 1403
select txid_current_snapshot(); -- 1402:1402:

*Question 2*: how is it possible that xmax is less than a current
transaction id? One can assume from session 1 that xmax = current
transaction id, so why xmax != 1403? And why is 1402 absent from a xip list?

*session 3:*
begin isolation level repeatable read;
update restaurant set address = '3' where id = 3;
select txid_current(); -- 1404
select txid_current_snapshot(); -- 1402:1402: -- all the same
rollback;

After then I commit the second transaction:

*session 2:*
commit;

... and start the fourth transaction:

*session 4:*
begin isolation level repeatable read;
update restaurant set address = '4' where id = 4;
select txid_current(); -- 1405
select txid_current_snapshot(); -- 1402:1405:1402

*Question 3*: why did a snapshot change so much? xmax is now 1405, and the
first transaction is finally in a xip list!

Browse pgsql-general by date

  From Date Subject
Next Message Hillary Masha 2022-08-29 14:50:35 Re: Corrupted Postgresql Microsoft Binaries
Previous Message Ajin Cherian 2022-08-29 06:14:57 Re: Support logical replication of DDLs