From c33906d95c65ac43ee341268a7c324d2c404ac56 Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Sun, 20 Jul 2025 18:19:47 +0530 Subject: [PATCH v20250723 1/6] Enhance pg_get_sequence_data function MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch enhances 'pg_get_sequence_data' to return the sequence’s log_cnt and associated page LSN. In the subsequent patches, When a sequence is synchronized to the subscriber, the page LSN of the sequence from the publisher is also captured and stored in pg_subscription_rel.srsublsn. This LSN will reflect the state of the sequence at the time of synchronization. By comparing the current LSN of the sequence on the publisher (via pg_sequence_state()) with the stored LSN on the subscriber, users can detect if the sequence has advanced and is now out-of-sync. This comparison will help determine whether re-synchronization is needed for a given sequence. Author: Vignesh C, Tomas Vondra Reviewer: Amit Kapila, Shveta Malik, Dilip Kumar, Peter Smith, Nisha Moond Discussion: https://www.postgresql.org/message-id/CAA4eK1LC+KJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ@mail.gmail.com --- doc/src/sgml/func.sgml | 26 ++++++++++++++++++++++++++ src/backend/commands/sequence.c | 16 +++++++++++++--- src/include/catalog/pg_proc.dat | 6 +++--- src/test/regress/expected/sequence.out | 8 ++++---- src/test/regress/sql/sequence.sql | 2 +- 5 files changed, 47 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index de5b5929ee0..5189dd754f7 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19945,6 +19945,32 @@ SELECT setval('myseq', 42, false); Next nextvalSELECT privilege on the last used sequence. + + + + + pg_get_sequence_data + + pg_get_sequence_data ( regclass ) + record + ( last_value bigint, + is_called bool, + log_cnt bigint, + page_lsn pg_lsn ) + + + Returns information about the sequence. last_value + is the current value of the sequence, is_called + indicates whether the sequence has been used, log_cnt + shows how many fetches remain before a new WAL record must be written, + and page_lsn is the page LSN of the sequence + relation. + + + This function requires USAGE + or SELECT privilege on the sequence. + + diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c index 451ae6f7f69..f5fa49517cf 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -45,6 +45,7 @@ #include "utils/acl.h" #include "utils/builtins.h" #include "utils/lsyscache.h" +#include "utils/pg_lsn.h" #include "utils/resowner.h" #include "utils/syscache.h" #include "utils/varlena.h" @@ -1778,15 +1779,16 @@ pg_sequence_parameters(PG_FUNCTION_ARGS) /* - * Return the sequence tuple. + * Return the sequence tuple along with its page LSN. * * This is primarily intended for use by pg_dump to gather sequence data - * without needing to individually query each sequence relation. + * without needing to individually query each sequence relation. This will + * also be used by logical replication while synchronizing sequences. */ Datum pg_get_sequence_data(PG_FUNCTION_ARGS) { -#define PG_GET_SEQUENCE_DATA_COLS 2 +#define PG_GET_SEQUENCE_DATA_COLS 4 Oid relid = PG_GETARG_OID(0); SeqTable elm; Relation seqrel; @@ -1801,6 +1803,10 @@ pg_get_sequence_data(PG_FUNCTION_ARGS) INT8OID, -1, 0); TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "is_called", BOOLOID, -1, 0); + TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "log_cnt", + INT8OID, -1, 0); + TupleDescInitEntry(resultTupleDesc, (AttrNumber) 4, "page_lsn", + LSNOID, -1, 0); resultTupleDesc = BlessTupleDesc(resultTupleDesc); init_sequence(relid, &elm, &seqrel); @@ -1816,11 +1822,15 @@ pg_get_sequence_data(PG_FUNCTION_ARGS) Buffer buf; HeapTupleData seqtuple; Form_pg_sequence_data seq; + Page page; seq = read_seq_tuple(seqrel, &buf, &seqtuple); + page = BufferGetPage(buf); values[0] = Int64GetDatum(seq->last_value); values[1] = BoolGetDatum(seq->is_called); + values[2] = Int64GetDatum(seq->log_cnt); + values[3] = LSNGetDatum(PageGetLSN(page)); UnlockReleaseBuffer(buf); } diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 3ee8fed7e53..9cc52a7c83f 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3433,11 +3433,11 @@ proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u', prorettype => 'int8', proargtypes => 'regclass', prosrc => 'pg_sequence_last_value' }, -{ oid => '6427', descr => 'return sequence tuple, for use by pg_dump', +{ oid => '6427', descr => 'return sequence tuple, for use by pg_dump and sequence synchronization', proname => 'pg_get_sequence_data', provolatile => 'v', proparallel => 'u', prorettype => 'record', proargtypes => 'regclass', - proallargtypes => '{regclass,int8,bool}', proargmodes => '{i,o,o}', - proargnames => '{sequence_oid,last_value,is_called}', + proallargtypes => '{regclass,int8,bool,int8,pg_lsn}', proargmodes => '{i,o,o,o,o}', + proargnames => '{sequence_oid,last_value,is_called,log_cnt,page_lsn}', prosrc => 'pg_get_sequence_data' }, { oid => '275', descr => 'return the next oid for a system table', diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out index 15925d99c8a..452e1f0cc28 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -840,10 +840,10 @@ SELECT nextval('test_seq1'); (1 row) -- pg_get_sequence_data -SELECT * FROM pg_get_sequence_data('test_seq1'); - last_value | is_called -------------+----------- - 10 | t +SELECT last_value, is_called, log_cnt FROM pg_get_sequence_data('test_seq1'); + last_value | is_called | log_cnt +------------+-----------+--------- + 10 | t | 32 (1 row) DROP SEQUENCE test_seq1; diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql index 2c220b60749..b772d5166d5 100644 --- a/src/test/regress/sql/sequence.sql +++ b/src/test/regress/sql/sequence.sql @@ -414,6 +414,6 @@ SELECT nextval('test_seq1'); SELECT nextval('test_seq1'); -- pg_get_sequence_data -SELECT * FROM pg_get_sequence_data('test_seq1'); +SELECT last_value, is_called, log_cnt FROM pg_get_sequence_data('test_seq1'); DROP SEQUENCE test_seq1; -- 2.43.0