v20250717-0001-Introduce-pg_sequence_state-function-for-e.patch

From 2001eacd1048c0af068f6258bf3912346562dd33 Mon Sep 17 00:00:00 2001
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Mon, 30 Jun 2025 10:12:45 +0530
Subject: [PATCH v20250717 1/6] Introduce pg_sequence_state function for
 enhanced sequence management

This patch introduces a new function, 'pg_sequence_state', which
allows retrieval of sequence values, including the associated 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.
---
 doc/src/sgml/func.sgml                 | 27 ++++++++++
 src/backend/commands/sequence.c        | 70 ++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat        |  8 +++
 src/test/regress/expected/sequence.out |  6 +++
 src/test/regress/sql/sequence.sql      |  1 +
 5 files changed, 112 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f5a0e0954a1..1c22f8278f2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19945,6 +19945,33 @@ 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_sequence_state</primary>
+        </indexterm>
+        <function>pg_sequence_state</function> ( <parameter>schema_name</parameter> <type>text</type>,
+        <parameter>sequence_name</parameter> <type>text</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>page_lsn</parameter> <type>pg_lsn</type>,
+        <parameter>last_value</parameter> <type>bigint</type>,
+        <parameter>log_cnt</parameter> <type>bigint</type>,
+        <parameter>is_called</parameter> <type>bool</type> )
+       </para>
+       <para>
+        Returns information about the sequence. <literal>page_lsn</literal> is
+        the page LSN of the sequence relation, <literal>last_value</literal> is
+        the current value of the sequence, <literal>log_cnt</literal> shows how
+        many fetches remain before a new WAL record must be written, and
+        <literal>is_called</literal> indicates whether the sequence has been
+        used.
+       </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..1f96d445f2d 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"
@@ -1885,6 +1886,75 @@ pg_sequence_last_value(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 }
 
+/*
+ * Return the current on-disk state of the sequence.
+ *
+ * Note: This is roughly equivalent to selecting the data from the sequence,
+ * except that it also returns the page LSN.
+ */
+Datum
+pg_sequence_state(PG_FUNCTION_ARGS)
+{
+	char	   *schema_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	char	   *sequence_name = text_to_cstring(PG_GETARG_TEXT_PP(1));
+	Oid			seq_relid;
+	SeqTable	elm;
+	Relation	seqrel;
+	Buffer		buf;
+	Page		page;
+	HeapTupleData seqtuple;
+	Form_pg_sequence_data seq;
+	Datum		result;
+
+	TupleDesc	tupdesc;
+	HeapTuple	tuple;
+	Datum		values[4];
+	bool		nulls[4] = {0};
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	seq_relid = RangeVarGetRelid(makeRangeVar(schema_name, sequence_name, -1),
+								 NoLock, true);
+	if (!OidIsValid(seq_relid))
+		ereport(ERROR,
+				errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("sequence \"%s.%s\" does not exist",
+					   schema_name, sequence_name));
+
+	/* Open and lock sequence */
+	init_sequence(seq_relid, &elm, &seqrel);
+
+	if (pg_class_aclcheck(elm->relid, GetUserId(),
+						  ACL_SELECT | ACL_USAGE) != ACLCHECK_OK)
+		ereport(ERROR,
+				errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				errmsg("permission denied for sequence %s",
+					   RelationGetRelationName(seqrel)));
+
+	seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+	page = BufferGetPage(buf);
+
+	/* Page LSN for the sequence */
+	values[0] = LSNGetDatum(PageGetLSN(page));
+
+	/* The value most recently returned by nextval in the current session */
+	values[1] = Int64GetDatum(seq->last_value);
+
+	/* How many fetches remain before a new WAL record must be written */
+	values[2] = Int64GetDatum(seq->log_cnt);
+
+	/* Indicates whether the sequence has been used */
+	values[3] = BoolGetDatum(seq->is_called);
+
+	UnlockReleaseBuffer(buf);
+	sequence_close(seqrel, NoLock);
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	PG_RETURN_DATUM(result);
+}
 
 void
 seq_redo(XLogReaderState *record)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1fc19146f46..96779df2941 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3433,6 +3433,14 @@
   proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
   prorettype => 'int8', proargtypes => 'regclass',
   prosrc => 'pg_sequence_last_value' },
+{ oid => '8051',
+  descr => 'current on-disk sequence state',
+  proname => 'pg_sequence_state', provolatile => 'v',
+  prorettype => 'record', proargtypes => 'text text',
+  proallargtypes => '{text,text,pg_lsn,int8,int8,bool}',
+  proargmodes => '{i,i,o,o,o,o}',
+  proargnames => '{schema_name,sequence_name,page_lsn,last_value,log_cnt,is_called}',
+  prosrc => 'pg_sequence_state' },
 { oid => '6427', descr => 'return sequence tuple, for use by pg_dump',
   proname => 'pg_get_sequence_data', provolatile => 'v', proparallel => 'u',
   prorettype => 'record', proargtypes => 'regclass',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 15925d99c8a..bc22e72a059 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -161,6 +161,12 @@ SELECT nextval('serialTest2_f6_seq');
 CREATE SEQUENCE sequence_test;
 CREATE SEQUENCE IF NOT EXISTS sequence_test;
 NOTICE:  relation "sequence_test" already exists, skipping
+SELECT last_value, log_cnt, is_called  FROM  pg_sequence_state('public', 'sequence_test');
+ last_value | log_cnt | is_called 
+------------+---------+-----------
+          1 |       0 | f
+(1 row)
+
 SELECT nextval('sequence_test'::text);
  nextval 
 ---------
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 2c220b60749..e8fd0d3c9fe 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -112,6 +112,7 @@ SELECT nextval('serialTest2_f6_seq');
 CREATE SEQUENCE sequence_test;
 CREATE SEQUENCE IF NOT EXISTS sequence_test;
 
+SELECT last_value, log_cnt, is_called  FROM  pg_sequence_state('public', 'sequence_test');
 SELECT nextval('sequence_test'::text);
 SELECT nextval('sequence_test'::regclass);
 SELECT currval('sequence_test'::text);
-- 
2.43.0