v20251006-0001-Enhance-pg_get_sequence_data-function.patch

From cadc820122cf4b62d1eee915b3e59b3c2a8847a6 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Mon, 6 Oct 2025 10:14:56 +0530
Subject: [PATCH v20251006] 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
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
---
 src/backend/commands/sequence.c        | 10 ++++++++--
 src/include/catalog/pg_proc.dat        |  4 ++--
 src/test/regress/expected/sequence.out |  8 ++++----
 src/test/regress/sql/sequence.sql      |  2 +-
 4 files changed, 15 insertions(+), 9 deletions(-)

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 636d3c3ec73..cf46a543364 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"
@@ -1795,7 +1796,7 @@ 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.
@@ -1803,7 +1804,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 Datum
 pg_get_sequence_data(PG_FUNCTION_ARGS)
 {
-#define PG_GET_SEQUENCE_DATA_COLS	2
+#define PG_GET_SEQUENCE_DATA_COLS	3
 	Oid			relid = PG_GETARG_OID(0);
 	SeqTable	elm;
 	Relation	seqrel;
@@ -1818,6 +1819,8 @@ pg_get_sequence_data(PG_FUNCTION_ARGS)
 					   INT8OID, -1, 0);
 	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "is_called",
 					   BOOLOID, -1, 0);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "page_lsn",
+					   LSNOID, -1, 0);
 	resultTupleDesc = BlessTupleDesc(resultTupleDesc);
 
 	init_sequence(relid, &elm, &seqrel);
@@ -1833,11 +1836,14 @@ 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] = LSNGetDatum(PageGetLSN(page));
 
 		UnlockReleaseBuffer(buf);
 	}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 01eba3b5a19..fa8686ae00b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3436,8 +3436,8 @@
 { oid => '6427', descr => 'return sequence tuple, for use by pg_dump',
   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,pg_lsn}', proargmodes => '{i,o,o,o}',
+  proargnames => '{sequence_oid,last_value,is_called,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..c4454e5b435 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, page_lsn <= pg_current_wal_lsn() as lsn FROM pg_get_sequence_data('test_seq1');
+ last_value | is_called | lsn 
+------------+-----------+-----
+         10 | t         | 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..b3344537929 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, page_lsn <= pg_current_wal_lsn() as lsn FROM pg_get_sequence_data('test_seq1');
 
 DROP SEQUENCE test_seq1;
-- 
2.43.0