pg_visible_in_snapshot clarification

From: Mike Roest <mike(dot)roest(at)replicon(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: pg_visible_in_snapshot clarification
Date: 2023-08-28 18:34:43
Message-ID: CAE7ByhjsN4SkdQsk6Z2M+zJQi7NsmS7SjNW9acG1Sk0wco0s+g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alan Stange 2023-08-28 20:06:21 auto vacuum question
Previous Message David G. Johnston 2023-08-28 14:44:09 Re: ident auth does not works as usual