From 164c35ff0a74a457d3c37f791cc428805186cd3c Mon Sep 17 00:00:00 2001 From: Nisha Moond Date: Mon, 30 Jun 2025 10:12:45 +0530 Subject: [PATCH v20250716 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 f5a0e0954a1..4fe158002a9 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19945,6 +19945,33 @@ SELECT setval('myseq', 42, false); Next nextvalSELECT privilege on the last used sequence. + + + + + pg_sequence_state + + pg_sequence_state ( schema_name text, + sequence_name text ) + record + ( page_lsn pg_lsn, + last_value bigint, + log_cnt bigint, + is_called bool ) + + + Returns information about the sequence. page_lsn is + the page LSN of the sequence, last_value is the + current value of the sequence, log_cnt shows how + many fetches remain before a new WAL record must be written, and + is_called indicates whether the sequence has been + used. + + + This function requires USAGE + or SELECT privilege on the sequence. + + 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