Thread: pg_visible_in_snapshot clarification

pg_visible_in_snapshot clarification

From

Mike Roest

Date:

28 August 2023, 21:34:43

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>:

BEGIN TRANSACTION;

INSERT INTO test values (2, 'test 2');

SELECT pg_current_xact_id();

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;

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

Re: pg_visible_in_snapshot clarification

From

Luca Ferrari

Date:

31 August 2023, 18:12:22

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