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

From 723d26a8192cac95ff7f31874b56df2c3d71c416 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Fri, 20 Sep 2024 08:45:21 +0530
Subject: [PATCH v20250622 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 a6d79765c1a..9d677b614b7 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..cf357650a24 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_OBJECT_NOT_IN_PREREQUISITE_STATE),
+				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 d3d28a263fa..eacb553075e 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 => '9876', 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