From 43d330cb5a0164076c8ef9f5d172d7f644527598 Mon Sep 17 00:00:00 2001 From: Nisha Moond Date: Mon, 30 Jun 2025 10:12:45 +0530 Subject: [PATCH v20250630 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 298791858be..b33ae0447b0 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 fb4f7f50350..b0e60dfa3ce 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.34.1