v20250801-0001-Enhance-pg_get_sequence_data-function.patch

From b78678d5171746211f53d1608f566885cb50aac7 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Sun, 20 Jul 2025 18:19:47 +0530
Subject: [PATCH v20250801 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 74a16af04ad..af63e1b6116 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19948,6 +19948,32 @@ SELECT setval('myseq', 42, false);    <lineannotation>Next <function>nextval</fu
         or <literal>SELECT</literal> privilege on the last used sequence.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_sequence_data</primary>
+        </indexterm>
+        <function>pg_get_sequence_data</function> ( <type>regclass</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>last_value</parameter> <type>bigint</type>,
+        <parameter>is_called</parameter> <type>bool</type>,
+        <parameter>log_cnt</parameter> <type>bigint</type>,
+         <parameter>page_lsn</parameter> <type>pg_lsn</type> )
+       </para>
+       <para>
+        Returns information about the sequence. <literal>last_value</literal>
+        is the current value of the sequence, <literal>is_called</literal>
+        indicates whether the sequence has been used, <literal>log_cnt</literal>
+        shows how many fetches remain before a new WAL record must be written,
+        and <literal>page_lsn</literal> is the page LSN of the sequence
+        relation.
+       </para>
+       <para>
+        This function requires <literal>USAGE</literal>
+        or <literal>SELECT</literal> privilege on the sequence.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
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..87433e508ca 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, page_lsn <= pg_current_wal_lsn() as lsn FROM pg_get_sequence_data('test_seq1');
+ last_value | is_called | log_cnt | lsn 
+------------+-----------+---------+-----
+         10 | t         |      32 | t
 (1 row)
 
 DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 2c220b60749..c8adddbfa31 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, page_lsn <= pg_current_wal_lsn() as lsn FROM pg_get_sequence_data('test_seq1');
 
 DROP SEQUENCE test_seq1;
-- 
2.43.0