Обсуждение: pg_visible_in_snapshot clarification
Hey There,
I'm looking for some clarification around pg_visible_in_snapshot function. As it seems to not be working the way I would expect or the way the documentation is setup.
I've attempted this on pg13 and pg15 and am getting the same behaviour.
3 connections via psql 2 to the primary and 1 to the secondary
C1: primary
C2: primary
C3: secondary (setup using streaming replication with hot_standby_feedback on)
Reproduction:
C1:
CREATE TABLE test (
id integer,
val text);
BEGIN TRANSACTION;
INSERT INTO test values (1, 'test 1');
SELECT pg_current_xact_id();
C3:
SELECT pg_visible_in_snapshot('<xact id from select above>'::xid8,pg_current_snapshot()); -- returns false
SELECT pg_current_snapshot(); -- shows <xid from C1>:<xid from C1>:
C2
BEGIN TRANSACTION;
INSERT INTO test values (2, 'test 2');
SELECT pg_current_xact_id();
C3
SELECT pg_visible_in_snapshot('<xact id from C1 select above>'::xid8,pg_current_snapshot()); -- returns false
SELECT pg_visible_in_snapshot('<xact id from C2 select above>'::xid8,pg_current_snapshot()); -- returns false
SELECT pg_current_snapshot(); -- shows <xid from C1>:<xid from C1>:
C2:
COMMIT;
C3
SELECT pg_visible_in_snapshot('<xact id from C1 select above>'::xid8,pg_current_snapshot()); -- returns true
SELECT pg_visible_in_snapshot('<xact id from C2 select above>'::xid8,pg_current_snapshot()); -- returns true
SELECT pg_current_snapshot(); -- shows <xid from C1>:<xid from C2+1>:
This is where things don't make sense to me. I would expect
SELECT pg_visible_in_snapshot('<xact id from C1 select above>'::xid8,pg_current_snapshot()); -- returns true
to return false as the transaction on C1 is still open and not commited. The 1,test 1 record in the test table is not available on the secondary yet however
pg_visible_in_snapshot is returning true for it's xactid.
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.
So I'm guessing from this that pg_visible_in_snapshot is not safe to use between a primary and a secondary?
If anyone could provide any additional insight that would be amazing.
Thanks
On Mon, Aug 28, 2023 at 8:34 PM Mike Roest <mike.roest@replicon.com> wrote: > I think this has to do with the pg_current_snapshot not showing the transaction in the xip_list which appears to be emptyon both C1 (in the transaction) and C3 on the replica. However C2 pg_current_snapshot() does show C1 xactid as in progressin 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