v2-0001-Switch-pg_sequence_last_value-to-report-a-tuple-a.patch

From 940aadb33155b90843d6e07fedbe1c13b767c5ea Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Fri, 1 Dec 2023 09:37:24 +0900
Subject: [PATCH v2 01/10] Switch pg_sequence_last_value() to report a tuple
 and use it in pg_dump

This commit switches pg_sequence_last_value() to report a tuple made of
(last_value,is_called) that can be directly be used for the arguments of
setval() in a sequence.

Going forward with PostgreSQL 17, pg_dump and pg_sequences make use of
it instead of scanning the heap table assumed to always exist for a
sequence.

Note: this requires a catversion bump.
---
 src/include/catalog/pg_proc.dat      |  6 ++++--
 src/backend/catalog/system_views.sql |  6 +++++-
 src/backend/commands/sequence.c      | 19 +++++++++++++------
 src/bin/pg_dump/pg_dump.c            | 16 +++++++++++++---
 src/test/regress/expected/rules.out  |  7 ++++++-
 5 files changed, 41 insertions(+), 13 deletions(-)

diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 77e8b13764..33c995cd06 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3325,9 +3325,11 @@
   proargmodes => '{i,o,o,o,o,o,o,o}',
   proargnames => '{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option,cache_size,data_type}',
   prosrc => 'pg_sequence_parameters' },
-{ oid => '4032', descr => 'sequence last value',
+{ oid => '4032', descr => 'sequence last value data',
   proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
-  prorettype => 'int8', proargtypes => 'regclass',
+  prorettype => 'record', proargtypes => 'regclass',
+  proallargtypes => '{regclass,bool,int8}', proargmodes => '{i,o,o}',
+  proargnames => '{seqname,is_called,last_value}',
   prosrc => 'pg_sequence_last_value' },
 
 { oid => '275', descr => 'return the next oid for a system table',
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 11d18ed9dd..009940dd80 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -178,7 +178,11 @@ CREATE VIEW pg_sequences AS
         S.seqcache AS cache_size,
         CASE
             WHEN has_sequence_privilege(C.oid, 'SELECT,USAGE'::text)
-                THEN pg_sequence_last_value(C.oid)
+                THEN (SELECT
+                          CASE WHEN sl.is_called
+                              THEN sl.last_value ELSE NULL
+                          END
+                      FROM pg_sequence_last_value(C.oid) sl)
             ELSE NULL
         END AS last_value
     FROM pg_sequence S JOIN pg_class C ON (C.oid = S.seqrelid)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index da2ace79cc..9c94255f24 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1779,14 +1779,22 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 Datum
 pg_sequence_last_value(PG_FUNCTION_ARGS)
 {
+#define PG_SEQUENCE_LAST_VALUE_COLS		2
 	Oid			relid = PG_GETARG_OID(0);
+	Datum		values[PG_SEQUENCE_LAST_VALUE_COLS] = {0};
+	bool		nulls[PG_SEQUENCE_LAST_VALUE_COLS] = {0};
 	SeqTable	elm;
 	Relation	seqrel;
+	TupleDesc	tupdesc;
 	Buffer		buf;
 	HeapTupleData seqtuple;
 	Form_pg_sequence_data seq;
 	bool		is_called;
-	int64		result;
+	int64		last_value;
+
+	/* Build a tuple descriptor for our result type */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
 
 	/* open and lock sequence */
 	init_sequence(relid, &elm, &seqrel);
@@ -1800,15 +1808,14 @@ pg_sequence_last_value(PG_FUNCTION_ARGS)
 	seq = read_seq_tuple(seqrel, &buf, &seqtuple);
 
 	is_called = seq->is_called;
-	result = seq->last_value;
+	last_value = seq->last_value;
 
 	UnlockReleaseBuffer(buf);
 	relation_close(seqrel, NoLock);
 
-	if (is_called)
-		PG_RETURN_INT64(result);
-	else
-		PG_RETURN_NULL();
+	values[0] = BoolGetDatum(is_called);
+	values[1] = Int64GetDatum(last_value);
+	PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
 }
 
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8c0b5486b9..c7612c3793 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -17476,9 +17476,19 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 	bool		called;
 	PQExpBuffer query = createPQExpBuffer();
 
-	appendPQExpBuffer(query,
-					  "SELECT last_value, is_called FROM %s",
-					  fmtQualifiedDumpable(tbinfo));
+	/*
+	 * In versions 17 and up, pg_sequence_last_value() has been switched to
+	 * return a tuple with last_value and is_called.
+	 */
+	if (fout->remoteVersion >= 170000)
+		appendPQExpBuffer(query,
+						  "SELECT last_value, is_called "
+						  "FROM pg_sequence_last_value('%s')",
+						  fmtQualifiedDumpable(tbinfo));
+	else
+		appendPQExpBuffer(query,
+						  "SELECT last_value, is_called FROM %s",
+						  fmtQualifiedDumpable(tbinfo));
 
 	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 05070393b9..105e8f5eb4 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1696,7 +1696,12 @@ pg_sequences| SELECT n.nspname AS schemaname,
     s.seqcycle AS cycle,
     s.seqcache AS cache_size,
         CASE
-            WHEN has_sequence_privilege(c.oid, 'SELECT,USAGE'::text) THEN pg_sequence_last_value((c.oid)::regclass)
+            WHEN has_sequence_privilege(c.oid, 'SELECT,USAGE'::text) THEN ( SELECT
+                    CASE
+                        WHEN sl.is_called THEN sl.last_value
+                        ELSE NULL::bigint
+                    END AS "case"
+               FROM pg_sequence_last_value((c.oid)::regclass) sl(is_called, last_value))
             ELSE NULL::bigint
         END AS last_value
    FROM ((pg_sequence s
-- 
2.43.0