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!
