pg_visible_in_snapshot clarification

Поиск
Список
Период
Сортировка
От Mike Roest
Тема pg_visible_in_snapshot clarification
Дата
Msg-id CAE7ByhjsN4SkdQsk6Z2M+zJQi7NsmS7SjNW9acG1Sk0wco0s+g@mail.gmail.com
обсуждение исходный текст
Ответы Re: pg_visible_in_snapshot clarification
Список 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





В списке pgsql-general по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: ident auth does not works as usual
Следующее
От: Alan Stange
Дата:
Сообщение: auto vacuum question