I've read some posts about how snapshots are built (
here,
here, and
here) 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!