On Mon, Aug 28, 2023 at 8:34 PM Mike Roest <mike.roest@xxxxxxxxxxxx> wrote: > I think this has to do with the pg_current_snapshot not showing the transaction in the xip_list which appears to be empty on both C1 (in the transaction) and C3 on the replica. However C2 pg_current_snapshot() does show C1 xactid as in progress in the xip_list. Effectively there is something that I don't understand too. I've reproduced the experiment, with two connections on the primary and one on the standby (PostgreSQL 15). First connection at the primary: testdb=*> INSERT INTO t( t ) SELECT 'XID = ' || txid_current() || ' PID = ' || pg_backend_pid() || ' SNAP = ' || pg_current_snapshot(); INSERT 0 1 testdb=*> SELECT * FROM t; id | t ----+-------------------------------------- 8 | XID = 875 PID = 1151 SNAP = 875:875: (1 row) Meanwhile, second connection to the primary: testdb=> BEGIN; BEGIN testdb=*> INSERT INTO t( t ) SELECT 'XID = ' || txid_current() || ' PID = ' || pg_backend_pid() || ' SNAP = ' || pg_current_snapshot(); INSERT 0 1 testdb=*> SELECT * FROM t; id | t ----+-------------------------------------- 9 | XID = 876 PID = 1200 SNAP = 875:875: (1 row) Meanwhile, third connection to the standby (physical replication with a slot): % psql -U luca -h venkman -p 6432 testdb psql (15.4 (Ubuntu 15.4-0ubuntu0.23.04.1), server 15.3) Type "help" for help. testdb=> SELECT pg_visible_in_snapshot( '875'::xid8, pg_current_snapshot() ), pg_current_snapshot(); pg_visible_in_snapshot | pg_current_snapshot ------------------------+--------------------- f | 875:875: (1 row) So far so good, then commit the second connection (on the primary) with xid 876, and on the standby: testdb=> SELECT pg_visible_in_snapshot( '875'::xid8, pg_current_snapshot() ), pg_current_snapshot(); pg_visible_in_snapshot | pg_current_snapshot ------------------------+--------------------- t | 875:877: Then I rollback the first connection (xid 875) and again, on the standby: testdb=> SELECT pg_visible_in_snapshot( '875'::xid8, pg_current_snapshot() ), pg_current_snapshot(); pg_visible_in_snapshot | pg_current_snapshot ------------------------+--------------------- t | 877:877: The latter result appears normal to me, since 875 is consolidated. But why is 875 visible when 876 commits and 875 does not? The same does not happen with only connections to the primary, that is not involving the replica node. Reproducing the same experiment, the third connections sees always a false against the first transaction (not commit) before and after the commit of the second transactions: testdb=> SELECT pg_visible_in_snapshot( '877'::xid8, pg_current_snapshot() ), pg_current_snapshot(); pg_visible_in_snapshot | pg_current_snapshot ------------------------+--------------------- f | 877:879:877 (1 row) -- second transacction 879 commits testdb=> SELECT pg_visible_in_snapshot( '877'::xid8, pg_current_snapshot() ), pg_current_snapshot(); pg_visible_in_snapshot | pg_current_snapshot ------------------------+--------------------- f | 877:880:877 (1 row) What I see, however, is that the snapshot is different in the case of local transacctions. I suspect that somehow the list of active transactions is not propagated to the replica xip, that is therefore forced to look into the commit status. But I would like to get a better explanation. Luca
