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

From d845c60c3244f93d8c3745c23bb59ff53ffc471c 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 v20250711 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 subsequent patches, this function will be used to fetch the
sequence states from the publisher in order to synchronize them on
the subscriber.
---
 doc/src/sgml/func.sgml                 | 27 +++++++++
 src/backend/commands/sequence.c        | 80 ++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat        |  8 +++
 src/test/regress/expected/sequence.out |  6 ++
 src/test/regress/sql/sequence.sql      |  1 +
 5 files changed, 122 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c28aa71f570..97bbc7367e9 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, <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..d051adf4931 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,85 @@ 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;
+
+	XLogRecPtr	lsn;
+	int64		last_value;
+	int64		log_cnt;
+	bool		is_called;
+
+	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);
+
+	lsn = PageGetLSN(page);
+	last_value = seq->last_value;
+	log_cnt = seq->log_cnt;
+	is_called = seq->is_called;
+
+	UnlockReleaseBuffer(buf);
+	sequence_close(seqrel, NoLock);
+
+	/* Page LSN for the sequence */
+	values[0] = LSNGetDatum(lsn);
+
+	/* The value most recently returned by nextval in the current session */
+	values[1] = Int64GetDatum(last_value);
+
+	/* How many fetches remain before a new WAL record must be written */
+	values[2] = Int64GetDatum(log_cnt);
+
+	/* Indicates whether the sequence has been used */
+	values[3] = BoolGetDatum(is_called);
+
+	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