improve performance of pg_dump with many sequences

From 8cab570c9c5771d58860ff4e1d8cbd38e2792d80 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 30 Apr 2024 14:41:36 -0500
Subject: [PATCH v2 1/3] parse sequence information

---
 src/bin/pg_dump/pg_dump.c | 64 ++++++++++++++++-----------------------
 1 file changed, 26 insertions(+), 38 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..bbcbe581aa 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -17198,18 +17198,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
 	PGresult   *res;
-	char	   *startv,
-			   *incby,
-			   *maxv,
-			   *minv,
-			   *cache,
-			   *seqtype;
+	char		seqtype[10];
 	bool		cycled;
 	bool		is_ascending;
 	int64		default_minv,
-				default_maxv;
-	char		bufm[32],
-				bufx[32];
+				default_maxv,
+				minv,
+				maxv,
+				startv,
+				incby,
+				cache;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	char	   *qseqname;
@@ -17251,16 +17249,21 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  PQntuples(res)),
 				 tbinfo->dobj.name, PQntuples(res));
 
-	seqtype = PQgetvalue(res, 0, 0);
-	startv = PQgetvalue(res, 0, 1);
-	incby = PQgetvalue(res, 0, 2);
-	maxv = PQgetvalue(res, 0, 3);
-	minv = PQgetvalue(res, 0, 4);
-	cache = PQgetvalue(res, 0, 5);
+	Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+	strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+	seqtype[sizeof(seqtype) - 1] = '\0';
+
+	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
 	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
+	PQclear(res);
+
 	/* Calculate default limits for a sequence of this type */
-	is_ascending = (incby[0] != '-');
+	is_ascending = (incby >= 0);
 	if (strcmp(seqtype, "smallint") == 0)
 	{
 		default_minv = is_ascending ? 1 : PG_INT16_MIN;
@@ -17282,19 +17285,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 		default_minv = default_maxv = 0;	/* keep compiler quiet */
 	}
 
-	/*
-	 * 64-bit strtol() isn't very portable, so convert the limits to strings
-	 * and compare that way.
-	 */
-	snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
-	snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
-	/* Don't print minv/maxv if they match the respective default limit */
-	if (strcmp(minv, bufm) == 0)
-		minv = NULL;
-	if (strcmp(maxv, bufx) == 0)
-		maxv = NULL;
-
 	/*
 	 * Identity sequences are not to be dropped separately.
 	 */
@@ -17346,22 +17336,22 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 			appendPQExpBuffer(query, "    AS %s\n", seqtype);
 	}
 
-	appendPQExpBuffer(query, "    START WITH %s\n", startv);
+	appendPQExpBuffer(query, "    START WITH " INT64_FORMAT "\n", startv);
 
-	appendPQExpBuffer(query, "    INCREMENT BY %s\n", incby);
+	appendPQExpBuffer(query, "    INCREMENT BY " INT64_FORMAT "\n", incby);
 
-	if (minv)
-		appendPQExpBuffer(query, "    MINVALUE %s\n", minv);
+	if (minv != default_minv)
+		appendPQExpBuffer(query, "    MINVALUE " INT64_FORMAT "\n", minv);
 	else
 		appendPQExpBufferStr(query, "    NO MINVALUE\n");
 
-	if (maxv)
-		appendPQExpBuffer(query, "    MAXVALUE %s\n", maxv);
+	if (maxv != default_maxv)
+		appendPQExpBuffer(query, "    MAXVALUE " INT64_FORMAT "\n", maxv);
 	else
 		appendPQExpBufferStr(query, "    NO MAXVALUE\n");
 
 	appendPQExpBuffer(query,
-					  "    CACHE %s%s",
+					  "    CACHE " INT64_FORMAT "%s",
 					  cache, (cycled ? "\n    CYCLE" : ""));
 
 	if (tbinfo->is_identity_sequence)
@@ -17448,8 +17438,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 					 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
 					 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 	destroyPQExpBuffer(delqry);
 	free(qseqname);
-- 
2.39.3 (Apple Git-146)

From a4486daeb5376da0d81e5acccf63fcbec6196490 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v2 2/3] cache sequence information

---
 src/bin/pg_dump/pg_dump.c        | 142 +++++++++++++++++++++++++------
 src/tools/pgindent/typedefs.list |   1 +
 2 files changed, 115 insertions(+), 28 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bbcbe581aa..4e8af72148 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,18 @@ typedef struct
 	RelFileNumber toast_index_relfilenumber;	/* toast table index filenode */
 } BinaryUpgradeClassOidItem;
 
+typedef struct
+{
+	Oid			oid;
+	char		seqtype[10];
+	bool		cycled;
+	int64		minv;
+	int64		maxv;
+	int64		startv;
+	int64		incby;
+	int64		cache;
+} SequenceItem;
+
 typedef enum OidOptions
 {
 	zeroIsError = 1,
@@ -173,6 +185,10 @@ static int	nseclabels = 0;
 static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
 static int	nbinaryUpgradeClassOids = 0;
 
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int	nsequences = 0;
+
 /*
  * The default number of rows per INSERT when
  * --inserts is specified without --rows-per-insert
@@ -270,6 +286,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
 static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
 static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
 static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
 static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -992,6 +1009,9 @@ main(int argc, char **argv)
 	if (dopt.binary_upgrade)
 		collectBinaryUpgradeClassOids(fout);
 
+	/* Collect sequence information. */
+	collectSequences(fout);
+
 	/* Lastly, create dummy objects to represent the section boundaries */
 	boundaryObjs = createBoundaryObjects();
 
@@ -17189,6 +17209,63 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
 	free(qtabname);
 }
 
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+	SequenceItem v1 = *((const SequenceItem *) p1);
+	SequenceItem v2 = *((const SequenceItem *) p2);
+
+	return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information.  This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+	PGresult   *res;
+	const char *query;
+
+	query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+		"seqstart, seqincrement, "
+		"seqmax, seqmin, "
+		"seqcache, seqcycle "
+		"FROM pg_catalog.pg_sequence "
+		"ORDER BY seqrelid";
+
+	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+	nsequences = PQntuples(res);
+	sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+	for (int i = 0; i < nsequences; i++)
+	{
+		size_t		seqtype_sz = sizeof(((SequenceItem *) 0)->seqtype);
+
+		sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+
+		Assert(strlen(PQgetvalue(res, i, 1)) < seqtype_sz);
+		strncpy(sequences[i].seqtype, PQgetvalue(res, i, 1), seqtype_sz);
+		sequences[i].seqtype[seqtype_sz - 1] = '\0';
+
+		sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+		sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+		sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+	}
+
+	PQclear(res);
+}
+
 /*
  * dumpSequence
  *	  write the declaration (not data) of one user-defined sequence
@@ -17197,7 +17274,6 @@ static void
 dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
-	PGresult   *res;
 	char		seqtype[10];
 	bool		cycled;
 	bool		is_ascending;
@@ -17217,17 +17293,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 
 	if (fout->remoteVersion >= 100000)
 	{
-		appendPQExpBuffer(query,
-						  "SELECT format_type(seqtypid, NULL), "
-						  "seqstart, seqincrement, "
-						  "seqmax, seqmin, "
-						  "seqcache, seqcycle "
-						  "FROM pg_catalog.pg_sequence "
-						  "WHERE seqrelid = '%u'::oid",
-						  tbinfo->dobj.catId.oid);
+		SequenceItem key = {0};
+		SequenceItem *entry;
+
+		Assert(sequences);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		entry = bsearch(&key, sequences, nsequences,
+						sizeof(SequenceItem), SequenceItemCmp);
+
+		strncpy(seqtype, entry->seqtype, sizeof(seqtype));
+		startv = entry->startv;
+		incby = entry->incby;
+		maxv = entry->maxv;
+		minv = entry->minv;
+		cache = entry->cache;
+		cycled = entry->cycled;
 	}
 	else
 	{
+		PGresult   *res;
+
 		/*
 		 * Before PostgreSQL 10, sequence metadata is in the sequence itself.
 		 *
@@ -17239,28 +17325,28 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  "start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
 						  fmtQualifiedDumpable(tbinfo));
-	}
-
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
-
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
 
-	Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
-	strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
-	seqtype[sizeof(seqtype) - 1] = '\0';
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
-	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
-	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
-	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
-	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
-	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+		strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+		seqtype[sizeof(seqtype) - 1] = '\0';
+
+		startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+		incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+		maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+		minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+		cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+		cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
-	PQclear(res);
+		PQclear(res);
+	}
 
 	/* Calculate default limits for a sequence of this type */
 	is_ascending = (incby >= 0);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 635e6d6e21..bae38ee4d1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2571,6 +2571,7 @@ SeqScan
 SeqScanState
 SeqTable
 SeqTableData
+SequenceItem
 SerCommitSeqNo
 SerialControl
 SerialIOData
-- 
2.39.3 (Apple Git-146)

From a6ed41b4cbed0a04fb3ab5cee6aeee4ed8c8cdf1 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 30 Apr 2024 19:33:24 -0500
Subject: [PATCH v2 3/3] cache more sequence data

---
 src/bin/pg_dump/pg_dump.c | 72 +++++++++++++++++++++++++++++----------
 1 file changed, 54 insertions(+), 18 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4e8af72148..6f0917c451 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -114,6 +114,8 @@ typedef struct
 	int64		startv;
 	int64		incby;
 	int64		cache;
+	int64		last_value;
+	bool		is_called;
 } SequenceItem;
 
 typedef enum OidOptions
@@ -17233,11 +17235,24 @@ collectSequences(Archive *fout)
 	PGresult   *res;
 	const char *query;
 
+	if (fout->remoteVersion < 100000)
+		return;
+
 	query = "SELECT seqrelid, format_type(seqtypid, NULL), "
 		"seqstart, seqincrement, "
 		"seqmax, seqmin, "
-		"seqcache, seqcycle "
-		"FROM pg_catalog.pg_sequence "
+		"seqcache, seqcycle, "
+		"CASE WHEN has_sequence_privilege(seqrelid, 'SELECT,USAGE'::text) "
+		"AND (pg_is_in_recovery() = 'f' OR c.relpersistence = 'p') "
+		"THEN pg_sequence_last_value(seqrelid) IS NOT NULL "
+		"ELSE 'f' END AS is_called, "
+		"CASE WHEN has_sequence_privilege(seqrelid, 'SELECT,USAGE'::text) "
+		"AND (pg_is_in_recovery() = 'f' OR c.relpersistence = 'p') "
+		"AND pg_sequence_last_value(seqrelid) IS NOT NULL "
+		"THEN pg_sequence_last_value(seqrelid) "
+		"ELSE seqstart END AS last_value "
+		"FROM pg_catalog.pg_sequence s "
+		"JOIN pg_class c ON s.seqrelid = c.oid "
 		"ORDER BY seqrelid";
 
 	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
@@ -17261,6 +17276,8 @@ collectSequences(Archive *fout)
 		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
 		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
 		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+		sequences[i].is_called = (strcmp(PQgetvalue(res, i, 8), "t") == 0);
+		sequences[i].last_value = strtoi64(PQgetvalue(res, i, 9), NULL, 10);
 	}
 
 	PQclear(res);
@@ -17537,30 +17554,51 @@ static void
 dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 {
 	TableInfo  *tbinfo = tdinfo->tdtable;
-	PGresult   *res;
-	char	   *last;
+	int64		last;
 	bool		called;
 	PQExpBuffer query = createPQExpBuffer();
 
-	appendPQExpBuffer(query,
-					  "SELECT last_value, is_called FROM %s",
-					  fmtQualifiedDumpable(tbinfo));
+	if (fout->remoteVersion < 100000)
+	{
+		PGresult   *res;
 
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+		appendPQExpBuffer(query,
+						  "SELECT last_value, is_called FROM %s",
+						  fmtQualifiedDumpable(tbinfo));
 
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	last = PQgetvalue(res, 0, 0);
-	called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+		called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+		PQclear(res);
+	}
+	else
+	{
+		SequenceItem key = {0};
+		SequenceItem *entry;
+
+		Assert(sequences);
+		Assert(tbinfo->dobj.catId.oid);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		entry = bsearch(&key, sequences, nsequences,
+						sizeof(SequenceItem), SequenceItemCmp);
+
+		last = entry->last_value;
+		called = entry->is_called;
+	}
 
 	resetPQExpBuffer(query);
 	appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
 	appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
-	appendPQExpBuffer(query, ", %s, %s);\n",
+	appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
 					  last, (called ? "true" : "false"));
 
 	if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17574,8 +17612,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 								  .deps = &(tbinfo->dobj.dumpId),
 								  .nDeps = 1));
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 }
 
-- 
2.39.3 (Apple Git-146)

From cc17b018e5f96df48e820a2d624ade7ceccfef18 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v3 1/3] parse sequence information

---
 src/bin/pg_dump/pg_dump.c | 64 ++++++++++++++++-----------------------
 1 file changed, 26 insertions(+), 38 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..bbcbe581aa 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -17198,18 +17198,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
 	PGresult   *res;
-	char	   *startv,
-			   *incby,
-			   *maxv,
-			   *minv,
-			   *cache,
-			   *seqtype;
+	char		seqtype[10];
 	bool		cycled;
 	bool		is_ascending;
 	int64		default_minv,
-				default_maxv;
-	char		bufm[32],
-				bufx[32];
+				default_maxv,
+				minv,
+				maxv,
+				startv,
+				incby,
+				cache;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	char	   *qseqname;
@@ -17251,16 +17249,21 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  PQntuples(res)),
 				 tbinfo->dobj.name, PQntuples(res));
 
-	seqtype = PQgetvalue(res, 0, 0);
-	startv = PQgetvalue(res, 0, 1);
-	incby = PQgetvalue(res, 0, 2);
-	maxv = PQgetvalue(res, 0, 3);
-	minv = PQgetvalue(res, 0, 4);
-	cache = PQgetvalue(res, 0, 5);
+	Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+	strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+	seqtype[sizeof(seqtype) - 1] = '\0';
+
+	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
 	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
+	PQclear(res);
+
 	/* Calculate default limits for a sequence of this type */
-	is_ascending = (incby[0] != '-');
+	is_ascending = (incby >= 0);
 	if (strcmp(seqtype, "smallint") == 0)
 	{
 		default_minv = is_ascending ? 1 : PG_INT16_MIN;
@@ -17282,19 +17285,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 		default_minv = default_maxv = 0;	/* keep compiler quiet */
 	}
 
-	/*
-	 * 64-bit strtol() isn't very portable, so convert the limits to strings
-	 * and compare that way.
-	 */
-	snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
-	snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
-	/* Don't print minv/maxv if they match the respective default limit */
-	if (strcmp(minv, bufm) == 0)
-		minv = NULL;
-	if (strcmp(maxv, bufx) == 0)
-		maxv = NULL;
-
 	/*
 	 * Identity sequences are not to be dropped separately.
 	 */
@@ -17346,22 +17336,22 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 			appendPQExpBuffer(query, "    AS %s\n", seqtype);
 	}
 
-	appendPQExpBuffer(query, "    START WITH %s\n", startv);
+	appendPQExpBuffer(query, "    START WITH " INT64_FORMAT "\n", startv);
 
-	appendPQExpBuffer(query, "    INCREMENT BY %s\n", incby);
+	appendPQExpBuffer(query, "    INCREMENT BY " INT64_FORMAT "\n", incby);
 
-	if (minv)
-		appendPQExpBuffer(query, "    MINVALUE %s\n", minv);
+	if (minv != default_minv)
+		appendPQExpBuffer(query, "    MINVALUE " INT64_FORMAT "\n", minv);
 	else
 		appendPQExpBufferStr(query, "    NO MINVALUE\n");
 
-	if (maxv)
-		appendPQExpBuffer(query, "    MAXVALUE %s\n", maxv);
+	if (maxv != default_maxv)
+		appendPQExpBuffer(query, "    MAXVALUE " INT64_FORMAT "\n", maxv);
 	else
 		appendPQExpBufferStr(query, "    NO MAXVALUE\n");
 
 	appendPQExpBuffer(query,
-					  "    CACHE %s%s",
+					  "    CACHE " INT64_FORMAT "%s",
 					  cache, (cycled ? "\n    CYCLE" : ""));
 
 	if (tbinfo->is_identity_sequence)
@@ -17448,8 +17438,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 					 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
 					 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 	destroyPQExpBuffer(delqry);
 	free(qseqname);
-- 
2.39.3 (Apple Git-146)

From cca9b72850bb375443ce1979634a6a65410c3ee3 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v3 2/3] cache sequence information

---
 src/bin/pg_dump/pg_dump.c        | 155 +++++++++++++++++++++++++------
 src/tools/pgindent/typedefs.list |   1 +
 2 files changed, 128 insertions(+), 28 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bbcbe581aa..a54e32c7be 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,18 @@ typedef struct
 	RelFileNumber toast_index_relfilenumber;	/* toast table index filenode */
 } BinaryUpgradeClassOidItem;
 
+typedef struct
+{
+	Oid			oid;			/* sequence OID */
+	char		seqtype[10];	/* data type of sequence */
+	bool		cycled;			/* whether sequence cycles */
+	int64		minv;			/* minimum value */
+	int64		maxv;			/* maximum value */
+	int64		startv;			/* start value */
+	int64		incby;			/* increment value */
+	int64		cache;			/* cache size */
+} SequenceItem;
+
 typedef enum OidOptions
 {
 	zeroIsError = 1,
@@ -173,6 +185,10 @@ static int	nseclabels = 0;
 static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
 static int	nbinaryUpgradeClassOids = 0;
 
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int	nsequences = 0;
+
 /*
  * The default number of rows per INSERT when
  * --inserts is specified without --rows-per-insert
@@ -270,6 +286,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
 static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
 static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
 static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
 static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -992,6 +1009,9 @@ main(int argc, char **argv)
 	if (dopt.binary_upgrade)
 		collectBinaryUpgradeClassOids(fout);
 
+	/* Collect sequence information. */
+	collectSequences(fout);
+
 	/* Lastly, create dummy objects to represent the section boundaries */
 	boundaryObjs = createBoundaryObjects();
 
@@ -17189,6 +17209,71 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
 	free(qtabname);
 }
 
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+	SequenceItem v1 = *((const SequenceItem *) p1);
+	SequenceItem v2 = *((const SequenceItem *) p2);
+
+	return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information.  This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+	PGresult   *res;
+	const char *query;
+
+	/*
+	 * Before Postgres 10, sequence metadata is in the sequence itself.  We
+	 * could likely make use of the sorted table with some extra effort, but
+	 * for now it seems unlikely to be worth it.
+	 */
+	if (fout->remoteVersion < 100000)
+		return;
+
+	query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+		"seqstart, seqincrement, "
+		"seqmax, seqmin, "
+		"seqcache, seqcycle "
+		"FROM pg_catalog.pg_sequence "
+		"ORDER BY seqrelid";
+
+	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+	nsequences = PQntuples(res);
+	sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+	for (int i = 0; i < nsequences; i++)
+	{
+		size_t		seqtype_sz = sizeof(((SequenceItem *) 0)->seqtype);
+
+		sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+
+		Assert(strlen(PQgetvalue(res, i, 1)) < seqtype_sz);
+		strncpy(sequences[i].seqtype, PQgetvalue(res, i, 1), seqtype_sz);
+		sequences[i].seqtype[seqtype_sz - 1] = '\0';
+
+		sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+		sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+		sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+	}
+
+	PQclear(res);
+}
+
 /*
  * dumpSequence
  *	  write the declaration (not data) of one user-defined sequence
@@ -17197,7 +17282,6 @@ static void
 dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
-	PGresult   *res;
 	char		seqtype[10];
 	bool		cycled;
 	bool		is_ascending;
@@ -17215,19 +17299,34 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 
 	qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
 
+	/*
+	 * For versions >= 10, the sequence information is gathered in a sorted
+	 * table before any calls to dumpSequence().  See collectSequences() for
+	 * more information.
+	 */
 	if (fout->remoteVersion >= 100000)
 	{
-		appendPQExpBuffer(query,
-						  "SELECT format_type(seqtypid, NULL), "
-						  "seqstart, seqincrement, "
-						  "seqmax, seqmin, "
-						  "seqcache, seqcycle "
-						  "FROM pg_catalog.pg_sequence "
-						  "WHERE seqrelid = '%u'::oid",
-						  tbinfo->dobj.catId.oid);
+		SequenceItem key = {0};
+		SequenceItem *entry;
+
+		Assert(sequences);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		entry = bsearch(&key, sequences, nsequences,
+						sizeof(SequenceItem), SequenceItemCmp);
+
+		strncpy(seqtype, entry->seqtype, sizeof(seqtype));
+		startv = entry->startv;
+		incby = entry->incby;
+		maxv = entry->maxv;
+		minv = entry->minv;
+		cache = entry->cache;
+		cycled = entry->cycled;
 	}
 	else
 	{
+		PGresult   *res;
+
 		/*
 		 * Before PostgreSQL 10, sequence metadata is in the sequence itself.
 		 *
@@ -17239,28 +17338,28 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  "start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
 						  fmtQualifiedDumpable(tbinfo));
-	}
-
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
-
-	Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
-	strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
-	seqtype[sizeof(seqtype) - 1] = '\0';
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
-	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
-	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
-	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
-	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
-	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+		strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+		seqtype[sizeof(seqtype) - 1] = '\0';
+
+		startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+		incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+		maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+		minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+		cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+		cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
-	PQclear(res);
+		PQclear(res);
+	}
 
 	/* Calculate default limits for a sequence of this type */
 	is_ascending = (incby >= 0);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..a09adcfb90 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
 SeqScanState
 SeqTable
 SeqTableData
+SequenceItem
 SerCommitSeqNo
 SerialControl
 SerialIOData
-- 
2.39.3 (Apple Git-146)

From 457ab1ac24d5ee438f89b71e2829c593c032bb91 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 30 Apr 2024 19:33:24 -0500
Subject: [PATCH v3 3/3] cache more sequence data

---
 src/bin/pg_dump/pg_dump.c | 126 ++++++++++++++++++++++++++++++--------
 1 file changed, 102 insertions(+), 24 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a54e32c7be..fd738f7087 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -114,6 +114,8 @@ typedef struct
 	int64		startv;			/* start value */
 	int64		incby;			/* increment value */
 	int64		cache;			/* cache size */
+	int64		last_value;		/* last value of sequence */
+	bool		is_called;		/* whether nextval advances before returning */
 } SequenceItem;
 
 typedef enum OidOptions
@@ -17231,7 +17233,7 @@ static void
 collectSequences(Archive *fout)
 {
 	PGresult   *res;
-	const char *query;
+	PQExpBuffer query;
 
 	/*
 	 * Before Postgres 10, sequence metadata is in the sequence itself.  We
@@ -17241,14 +17243,63 @@ collectSequences(Archive *fout)
 	if (fout->remoteVersion < 100000)
 		return;
 
-	query = "SELECT seqrelid, format_type(seqtypid, NULL), "
-		"seqstart, seqincrement, "
-		"seqmax, seqmin, "
-		"seqcache, seqcycle "
-		"FROM pg_catalog.pg_sequence "
-		"ORDER BY seqrelid";
+	query = createPQExpBuffer();
 
-	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+	appendPQExpBuffer(query, "SELECT seqrelid, format_type(seqtypid, NULL), "
+					  "seqstart, seqincrement, "
+					  "seqmax, seqmin, "
+					  "seqcache, seqcycle, ");
+
+	if (fout->dopt->schemaOnly && !fout->dopt->sequence_data)
+	{
+		/*
+		 * If we aren't dumping the sequence data, just return garbage for
+		 * is_called and last_value.
+		 */
+		appendPQExpBuffer(query, "'f', NULL FROM pg_catalog.pg_sequence ");
+	}
+	else if (fout->remoteVersion >= 180000)
+	{
+		/*
+		 * Since version 18, pg_sequence_last_value() returns NULL if the
+		 * caller doesn't have privileges on the sequence (instead of
+		 * ERROR-ing), so we don't need to make any extra calls to
+		 * has_sequence_privilege() in the query.
+		 */
+		appendPQExpBuffer(query,
+						  "pg_sequence_last_value(seqrelid) IS NOT NULL, "
+						  "CASE WHEN pg_sequence_last_value(seqrelid) IS NOT NULL "
+						  "THEN pg_sequence_last_value(seqrelid) "
+						  "ELSE seqstart END "
+						  "FROM pg_catalog.pg_sequence ");
+	}
+	else
+	{
+		/*
+		 * Before v18, pg_sequence_last_value() ERRORs for sequences for which
+		 * the caller lacks privileges, so we must check privileges before
+		 * calling it.  Furthermore, some older versions have a buggy
+		 * pg_sequence_last_value() that ERRORs for unlogged sequences on
+		 * standbys (see commit 3cb2f13ac5), so we need some additional checks
+		 * to avoid that.
+		 */
+		appendPQExpBuffer(query,
+						  "CASE WHEN has_sequence_privilege(seqrelid, 'SELECT,USAGE'::text) "
+						  "AND (pg_is_in_recovery() = 'f' OR c.relpersistence = 'p') "
+						  "THEN pg_sequence_last_value(seqrelid) IS NOT NULL "
+						  "ELSE 'f' END, "
+						  "CASE WHEN has_sequence_privilege(seqrelid, 'SELECT,USAGE'::text) "
+						  "AND (pg_is_in_recovery() = 'f' OR c.relpersistence = 'p') "
+						  "AND pg_sequence_last_value(seqrelid) IS NOT NULL "
+						  "THEN pg_sequence_last_value(seqrelid) "
+						  "ELSE seqstart END "
+						  "FROM pg_catalog.pg_sequence s "
+						  "JOIN pg_class c ON s.seqrelid = c.oid ");
+	}
+
+	appendPQExpBuffer(query, "ORDER BY seqrelid");
+
+	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
 	nsequences = PQntuples(res);
 	sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
@@ -17269,9 +17320,12 @@ collectSequences(Archive *fout)
 		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
 		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
 		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+		sequences[i].is_called = (strcmp(PQgetvalue(res, i, 8), "t") == 0);
+		sequences[i].last_value = strtoi64(PQgetvalue(res, i, 9), NULL, 10);
 	}
 
 	PQclear(res);
+	destroyPQExpBuffer(query);
 }
 
 /*
@@ -17550,30 +17604,56 @@ static void
 dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 {
 	TableInfo  *tbinfo = tdinfo->tdtable;
-	PGresult   *res;
-	char	   *last;
+	int64		last;
 	bool		called;
 	PQExpBuffer query = createPQExpBuffer();
 
-	appendPQExpBuffer(query,
-					  "SELECT last_value, is_called FROM %s",
-					  fmtQualifiedDumpable(tbinfo));
+	/*
+	 * For versions >= 10, the sequence information is gathered in a sorted
+	 * table before any calls to dumpSequenceData().  See collectSequences()
+	 * for more information.
+	 */
+	if (fout->remoteVersion < 100000)
+	{
+		PGresult   *res;
 
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+		appendPQExpBuffer(query,
+						  "SELECT last_value, is_called FROM %s",
+						  fmtQualifiedDumpable(tbinfo));
 
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+		called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+		PQclear(res);
+	}
+	else
+	{
+		SequenceItem key = {0};
+		SequenceItem *entry;
 
-	last = PQgetvalue(res, 0, 0);
-	called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+		Assert(sequences);
+		Assert(tbinfo->dobj.catId.oid);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		entry = bsearch(&key, sequences, nsequences,
+						sizeof(SequenceItem), SequenceItemCmp);
+
+		last = entry->last_value;
+		called = entry->is_called;
+	}
 
 	resetPQExpBuffer(query);
 	appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
 	appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
-	appendPQExpBuffer(query, ", %s, %s);\n",
+	appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
 					  last, (called ? "true" : "false"));
 
 	if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17587,8 +17667,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 								  .deps = &(tbinfo->dobj.dumpId),
 								  .nDeps = 1));
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 }
 
-- 
2.39.3 (Apple Git-146)

From c5e139efdae92c3902daee886075b509a0720368 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v4 1/4] parse sequence information

---
 src/bin/pg_dump/pg_dump.c | 64 ++++++++++++++++-----------------------
 1 file changed, 26 insertions(+), 38 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..bbcbe581aa 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -17198,18 +17198,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
 	PGresult   *res;
-	char	   *startv,
-			   *incby,
-			   *maxv,
-			   *minv,
-			   *cache,
-			   *seqtype;
+	char		seqtype[10];
 	bool		cycled;
 	bool		is_ascending;
 	int64		default_minv,
-				default_maxv;
-	char		bufm[32],
-				bufx[32];
+				default_maxv,
+				minv,
+				maxv,
+				startv,
+				incby,
+				cache;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	char	   *qseqname;
@@ -17251,16 +17249,21 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  PQntuples(res)),
 				 tbinfo->dobj.name, PQntuples(res));
 
-	seqtype = PQgetvalue(res, 0, 0);
-	startv = PQgetvalue(res, 0, 1);
-	incby = PQgetvalue(res, 0, 2);
-	maxv = PQgetvalue(res, 0, 3);
-	minv = PQgetvalue(res, 0, 4);
-	cache = PQgetvalue(res, 0, 5);
+	Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+	strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+	seqtype[sizeof(seqtype) - 1] = '\0';
+
+	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
 	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
+	PQclear(res);
+
 	/* Calculate default limits for a sequence of this type */
-	is_ascending = (incby[0] != '-');
+	is_ascending = (incby >= 0);
 	if (strcmp(seqtype, "smallint") == 0)
 	{
 		default_minv = is_ascending ? 1 : PG_INT16_MIN;
@@ -17282,19 +17285,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 		default_minv = default_maxv = 0;	/* keep compiler quiet */
 	}
 
-	/*
-	 * 64-bit strtol() isn't very portable, so convert the limits to strings
-	 * and compare that way.
-	 */
-	snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
-	snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
-	/* Don't print minv/maxv if they match the respective default limit */
-	if (strcmp(minv, bufm) == 0)
-		minv = NULL;
-	if (strcmp(maxv, bufx) == 0)
-		maxv = NULL;
-
 	/*
 	 * Identity sequences are not to be dropped separately.
 	 */
@@ -17346,22 +17336,22 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 			appendPQExpBuffer(query, "    AS %s\n", seqtype);
 	}
 
-	appendPQExpBuffer(query, "    START WITH %s\n", startv);
+	appendPQExpBuffer(query, "    START WITH " INT64_FORMAT "\n", startv);
 
-	appendPQExpBuffer(query, "    INCREMENT BY %s\n", incby);
+	appendPQExpBuffer(query, "    INCREMENT BY " INT64_FORMAT "\n", incby);
 
-	if (minv)
-		appendPQExpBuffer(query, "    MINVALUE %s\n", minv);
+	if (minv != default_minv)
+		appendPQExpBuffer(query, "    MINVALUE " INT64_FORMAT "\n", minv);
 	else
 		appendPQExpBufferStr(query, "    NO MINVALUE\n");
 
-	if (maxv)
-		appendPQExpBuffer(query, "    MAXVALUE %s\n", maxv);
+	if (maxv != default_maxv)
+		appendPQExpBuffer(query, "    MAXVALUE " INT64_FORMAT "\n", maxv);
 	else
 		appendPQExpBufferStr(query, "    NO MAXVALUE\n");
 
 	appendPQExpBuffer(query,
-					  "    CACHE %s%s",
+					  "    CACHE " INT64_FORMAT "%s",
 					  cache, (cycled ? "\n    CYCLE" : ""));
 
 	if (tbinfo->is_identity_sequence)
@@ -17448,8 +17438,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 					 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
 					 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 	destroyPQExpBuffer(delqry);
 	free(qseqname);
-- 
2.39.3 (Apple Git-146)

From b7fcaba2c5eef8824153c263c2df516783f0225b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v4 2/4] cache sequence information

---
 src/bin/pg_dump/pg_dump.c        | 155 +++++++++++++++++++++++++------
 src/tools/pgindent/typedefs.list |   1 +
 2 files changed, 128 insertions(+), 28 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bbcbe581aa..a54e32c7be 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,18 @@ typedef struct
 	RelFileNumber toast_index_relfilenumber;	/* toast table index filenode */
 } BinaryUpgradeClassOidItem;
 
+typedef struct
+{
+	Oid			oid;			/* sequence OID */
+	char		seqtype[10];	/* data type of sequence */
+	bool		cycled;			/* whether sequence cycles */
+	int64		minv;			/* minimum value */
+	int64		maxv;			/* maximum value */
+	int64		startv;			/* start value */
+	int64		incby;			/* increment value */
+	int64		cache;			/* cache size */
+} SequenceItem;
+
 typedef enum OidOptions
 {
 	zeroIsError = 1,
@@ -173,6 +185,10 @@ static int	nseclabels = 0;
 static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
 static int	nbinaryUpgradeClassOids = 0;
 
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int	nsequences = 0;
+
 /*
  * The default number of rows per INSERT when
  * --inserts is specified without --rows-per-insert
@@ -270,6 +286,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
 static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
 static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
 static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
 static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -992,6 +1009,9 @@ main(int argc, char **argv)
 	if (dopt.binary_upgrade)
 		collectBinaryUpgradeClassOids(fout);
 
+	/* Collect sequence information. */
+	collectSequences(fout);
+
 	/* Lastly, create dummy objects to represent the section boundaries */
 	boundaryObjs = createBoundaryObjects();
 
@@ -17189,6 +17209,71 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
 	free(qtabname);
 }
 
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+	SequenceItem v1 = *((const SequenceItem *) p1);
+	SequenceItem v2 = *((const SequenceItem *) p2);
+
+	return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information.  This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+	PGresult   *res;
+	const char *query;
+
+	/*
+	 * Before Postgres 10, sequence metadata is in the sequence itself.  We
+	 * could likely make use of the sorted table with some extra effort, but
+	 * for now it seems unlikely to be worth it.
+	 */
+	if (fout->remoteVersion < 100000)
+		return;
+
+	query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+		"seqstart, seqincrement, "
+		"seqmax, seqmin, "
+		"seqcache, seqcycle "
+		"FROM pg_catalog.pg_sequence "
+		"ORDER BY seqrelid";
+
+	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+	nsequences = PQntuples(res);
+	sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+	for (int i = 0; i < nsequences; i++)
+	{
+		size_t		seqtype_sz = sizeof(((SequenceItem *) 0)->seqtype);
+
+		sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+
+		Assert(strlen(PQgetvalue(res, i, 1)) < seqtype_sz);
+		strncpy(sequences[i].seqtype, PQgetvalue(res, i, 1), seqtype_sz);
+		sequences[i].seqtype[seqtype_sz - 1] = '\0';
+
+		sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+		sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+		sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+	}
+
+	PQclear(res);
+}
+
 /*
  * dumpSequence
  *	  write the declaration (not data) of one user-defined sequence
@@ -17197,7 +17282,6 @@ static void
 dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
-	PGresult   *res;
 	char		seqtype[10];
 	bool		cycled;
 	bool		is_ascending;
@@ -17215,19 +17299,34 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 
 	qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
 
+	/*
+	 * For versions >= 10, the sequence information is gathered in a sorted
+	 * table before any calls to dumpSequence().  See collectSequences() for
+	 * more information.
+	 */
 	if (fout->remoteVersion >= 100000)
 	{
-		appendPQExpBuffer(query,
-						  "SELECT format_type(seqtypid, NULL), "
-						  "seqstart, seqincrement, "
-						  "seqmax, seqmin, "
-						  "seqcache, seqcycle "
-						  "FROM pg_catalog.pg_sequence "
-						  "WHERE seqrelid = '%u'::oid",
-						  tbinfo->dobj.catId.oid);
+		SequenceItem key = {0};
+		SequenceItem *entry;
+
+		Assert(sequences);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		entry = bsearch(&key, sequences, nsequences,
+						sizeof(SequenceItem), SequenceItemCmp);
+
+		strncpy(seqtype, entry->seqtype, sizeof(seqtype));
+		startv = entry->startv;
+		incby = entry->incby;
+		maxv = entry->maxv;
+		minv = entry->minv;
+		cache = entry->cache;
+		cycled = entry->cycled;
 	}
 	else
 	{
+		PGresult   *res;
+
 		/*
 		 * Before PostgreSQL 10, sequence metadata is in the sequence itself.
 		 *
@@ -17239,28 +17338,28 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  "start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
 						  fmtQualifiedDumpable(tbinfo));
-	}
-
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
-
-	Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
-	strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
-	seqtype[sizeof(seqtype) - 1] = '\0';
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
-	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
-	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
-	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
-	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
-	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+		strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+		seqtype[sizeof(seqtype) - 1] = '\0';
+
+		startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+		incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+		maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+		minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+		cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+		cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
-	PQclear(res);
+		PQclear(res);
+	}
 
 	/* Calculate default limits for a sequence of this type */
 	is_ascending = (incby >= 0);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..a09adcfb90 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
 SeqScanState
 SeqTable
 SeqTableData
+SequenceItem
 SerCommitSeqNo
 SerialControl
 SerialIOData
-- 
2.39.3 (Apple Git-146)

From 86f0b7c26be5bd257a5039bdfbe127983e1228c7 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v4 3/4] introduce pg_sequence_read_tuple

XXX: NEEDS CATVERSION BUMP
---
 src/backend/commands/sequence.c        | 62 ++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat        |  6 +++
 src/test/regress/expected/sequence.out |  7 +++
 src/test/regress/sql/sequence.sql      |  3 ++
 4 files changed, 78 insertions(+)

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..45c4cb3936 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 	return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
 }
 
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	SeqTable	elm;
+	Relation	seqrel;
+	Datum		values[SEQ_COL_LASTCOL];
+	bool		isnull[SEQ_COL_LASTCOL];
+	TupleDesc	resultTupleDesc;
+	HeapTuple	resultHeapTuple;
+	Datum		result;
+
+	resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+					   BOOLOID, -1, 0);
+	resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+	init_sequence(relid, &elm, &seqrel);
+
+	/*
+	 * Return all NULLs for sequences for which we lack privileges, other
+	 * sessions' temporary sequences, and unlogged sequences on standbys.
+	 */
+	if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_USAGE) == ACLCHECK_OK &&
+		!RELATION_IS_OTHER_TEMP(seqrel) &&
+		(RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+	{
+		Buffer		buf;
+		HeapTupleData seqtuple;
+		Form_pg_sequence_data seq;
+
+		seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+		memset(isnull, false, sizeof(isnull));
+		values[0] = Int64GetDatum(seq->last_value);
+		values[1] = Int64GetDatum(seq->log_cnt);
+		values[2] = BoolGetDatum(seq->is_called);
+
+		UnlockReleaseBuffer(buf);
+	}
+	else
+		memset(isnull, true, sizeof(isnull));
+
+	sequence_close(seqrel, NoLock);
+
+	resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+	result = HeapTupleGetDatum(resultHeapTuple);
+	PG_RETURN_DATUM(result);
+}
+
+
 /*
  * Return the last value from the sequence
  *
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
   proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
   prorettype => 'int8', proargtypes => 'regclass',
   prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+  proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+  prorettype => 'record', proargtypes => 'regclass',
+  proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+  proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+  prosrc => 'pg_sequence_read_tuple' },
 
 { oid => '275', descr => 'return the next oid for a system table',
   proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
        3
 (1 row)
 
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called 
+------------+---------+-----------
+         10 |      32 | t
+(1 row)
+
 DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
 SELECT nextval('test_seq1');
 SELECT nextval('test_seq1');
 
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
 DROP SEQUENCE test_seq1;
-- 
2.39.3 (Apple Git-146)

From d22c7281416280480b91cff02330a858946364ad Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v4 4/4] cache sequence data

---
 src/bin/pg_dump/pg_dump.c | 91 +++++++++++++++++++++++++++++----------
 1 file changed, 68 insertions(+), 23 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a54e32c7be..14019907db 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -114,6 +114,8 @@ typedef struct
 	int64		startv;			/* start value */
 	int64		incby;			/* increment value */
 	int64		cache;			/* cache size */
+	int64		last_value;		/* last value of sequence */
+	bool		is_called;		/* whether nextval advances before returning */
 } SequenceItem;
 
 typedef enum OidOptions
@@ -17237,16 +17239,30 @@ collectSequences(Archive *fout)
 	 * Before Postgres 10, sequence metadata is in the sequence itself.  We
 	 * could likely make use of the sorted table with some extra effort, but
 	 * for now it seems unlikely to be worth it.
+	 *
+	 * Since version 18, we can gather the sequence data in this query with
+	 * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
 	 */
 	if (fout->remoteVersion < 100000)
 		return;
-
-	query = "SELECT seqrelid, format_type(seqtypid, NULL), "
-		"seqstart, seqincrement, "
-		"seqmax, seqmin, "
-		"seqcache, seqcycle "
-		"FROM pg_catalog.pg_sequence "
-		"ORDER BY seqrelid";
+	else if (fout->remoteVersion < 180000 ||
+			 (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
+		query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+			"seqstart, seqincrement, "
+			"seqmax, seqmin, "
+			"seqcache, seqcycle, "
+			"NULL, 'f' "
+			"FROM pg_catalog.pg_sequence "
+			"ORDER BY seqrelid";
+	else
+		query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+			"seqstart, seqincrement, "
+			"seqmax, seqmin, "
+			"seqcache, seqcycle, "
+			"last_value, is_called "
+			"FROM pg_catalog.pg_sequence, "
+			"pg_sequence_read_tuple(seqrelid) "
+			"ORDER BY seqrelid;";
 
 	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
 
@@ -17269,6 +17285,8 @@ collectSequences(Archive *fout)
 		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
 		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
 		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+		sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+		sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
 	}
 
 	PQclear(res);
@@ -17550,30 +17568,59 @@ static void
 dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 {
 	TableInfo  *tbinfo = tdinfo->tdtable;
-	PGresult   *res;
-	char	   *last;
+	int64		last;
 	bool		called;
 	PQExpBuffer query = createPQExpBuffer();
 
-	appendPQExpBuffer(query,
-					  "SELECT last_value, is_called FROM %s",
-					  fmtQualifiedDumpable(tbinfo));
+	/*
+	 * For versions >= 18, the sequence information is gathered in the sorted
+	 * array before any calls to dumpSequenceData().  See collectSequences()
+	 * for more information.
+	 *
+	 * For older versions, we have to query the sequence relations
+	 * individually.
+	 */
+	if (fout->remoteVersion < 180000)
+	{
+		PGresult   *res;
 
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+		appendPQExpBuffer(query,
+						  "SELECT last_value, is_called FROM %s",
+						  fmtQualifiedDumpable(tbinfo));
 
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	last = PQgetvalue(res, 0, 0);
-	called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+		called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+		PQclear(res);
+	}
+	else
+	{
+		SequenceItem key = {0};
+		SequenceItem *entry;
+
+		Assert(sequences);
+		Assert(tbinfo->dobj.catId.oid);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		entry = bsearch(&key, sequences, nsequences,
+						sizeof(SequenceItem), SequenceItemCmp);
+
+		last = entry->last_value;
+		called = entry->is_called;
+	}
 
 	resetPQExpBuffer(query);
 	appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
 	appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
-	appendPQExpBuffer(query, ", %s, %s);\n",
+	appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
 					  last, (called ? "true" : "false"));
 
 	if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17587,8 +17634,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 								  .deps = &(tbinfo->dobj.dumpId),
 								  .nDeps = 1));
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 }
 
-- 
2.39.3 (Apple Git-146)

From e98ff5c9a5e8962445de0d5ab68ce50ed40c121b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v5 1/4] parse sequence information

---
 src/bin/pg_dump/pg_dump.c        | 102 ++++++++++++++++++-------------
 src/tools/pgindent/typedefs.list |   1 +
 2 files changed, 59 insertions(+), 44 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..afafd641fe 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,23 @@ typedef struct
 	RelFileNumber toast_index_relfilenumber;	/* toast table index filenode */
 } BinaryUpgradeClassOidItem;
 
+typedef enum SeqType
+{
+	SEQTYPE_SMALLINT,
+	SEQTYPE_INTEGER,
+	SEQTYPE_BIGINT,
+} SeqType;
+
+const char *const SeqTypeNames[] =
+{
+	[SEQTYPE_SMALLINT] = "smallint",
+	[SEQTYPE_INTEGER] = "integer",
+	[SEQTYPE_BIGINT] = "bigint",
+};
+
+StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
+				 "array length mismatch");
+
 typedef enum OidOptions
 {
 	zeroIsError = 1,
@@ -17189,6 +17206,18 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
 	free(qtabname);
 }
 
+static inline SeqType
+parse_sequence_type(const char *name)
+{
+	for (int i = 0; i < lengthof(SeqTypeNames); i++)
+	{
+		if (strcmp(SeqTypeNames[i], name) == 0)
+			return (SeqType) i;
+	}
+
+	pg_fatal("unrecognized sequence type: %s", name);
+}
+
 /*
  * dumpSequence
  *	  write the declaration (not data) of one user-defined sequence
@@ -17198,18 +17227,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
 	PGresult   *res;
-	char	   *startv,
-			   *incby,
-			   *maxv,
-			   *minv,
-			   *cache,
-			   *seqtype;
+	SeqType		seqtype;
 	bool		cycled;
 	bool		is_ascending;
 	int64		default_minv,
-				default_maxv;
-	char		bufm[32],
-				bufx[32];
+				default_maxv,
+				minv,
+				maxv,
+				startv,
+				incby,
+				cache;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	char	   *qseqname;
@@ -17251,50 +17278,39 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  PQntuples(res)),
 				 tbinfo->dobj.name, PQntuples(res));
 
-	seqtype = PQgetvalue(res, 0, 0);
-	startv = PQgetvalue(res, 0, 1);
-	incby = PQgetvalue(res, 0, 2);
-	maxv = PQgetvalue(res, 0, 3);
-	minv = PQgetvalue(res, 0, 4);
-	cache = PQgetvalue(res, 0, 5);
+	seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
 	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
+	PQclear(res);
+
 	/* Calculate default limits for a sequence of this type */
-	is_ascending = (incby[0] != '-');
-	if (strcmp(seqtype, "smallint") == 0)
+	is_ascending = (incby >= 0);
+	if (seqtype == SEQTYPE_SMALLINT)
 	{
 		default_minv = is_ascending ? 1 : PG_INT16_MIN;
 		default_maxv = is_ascending ? PG_INT16_MAX : -1;
 	}
-	else if (strcmp(seqtype, "integer") == 0)
+	else if (seqtype == SEQTYPE_INTEGER)
 	{
 		default_minv = is_ascending ? 1 : PG_INT32_MIN;
 		default_maxv = is_ascending ? PG_INT32_MAX : -1;
 	}
-	else if (strcmp(seqtype, "bigint") == 0)
+	else if (seqtype == SEQTYPE_BIGINT)
 	{
 		default_minv = is_ascending ? 1 : PG_INT64_MIN;
 		default_maxv = is_ascending ? PG_INT64_MAX : -1;
 	}
 	else
 	{
-		pg_fatal("unrecognized sequence type: %s", seqtype);
+		pg_fatal("unrecognized sequence type: %d", seqtype);
 		default_minv = default_maxv = 0;	/* keep compiler quiet */
 	}
 
-	/*
-	 * 64-bit strtol() isn't very portable, so convert the limits to strings
-	 * and compare that way.
-	 */
-	snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
-	snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
-	/* Don't print minv/maxv if they match the respective default limit */
-	if (strcmp(minv, bufm) == 0)
-		minv = NULL;
-	if (strcmp(maxv, bufx) == 0)
-		maxv = NULL;
-
 	/*
 	 * Identity sequences are not to be dropped separately.
 	 */
@@ -17342,26 +17358,26 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  "UNLOGGED " : "",
 						  fmtQualifiedDumpable(tbinfo));
 
-		if (strcmp(seqtype, "bigint") != 0)
-			appendPQExpBuffer(query, "    AS %s\n", seqtype);
+		if (seqtype != SEQTYPE_BIGINT)
+			appendPQExpBuffer(query, "    AS %s\n", SeqTypeNames[seqtype]);
 	}
 
-	appendPQExpBuffer(query, "    START WITH %s\n", startv);
+	appendPQExpBuffer(query, "    START WITH " INT64_FORMAT "\n", startv);
 
-	appendPQExpBuffer(query, "    INCREMENT BY %s\n", incby);
+	appendPQExpBuffer(query, "    INCREMENT BY " INT64_FORMAT "\n", incby);
 
-	if (minv)
-		appendPQExpBuffer(query, "    MINVALUE %s\n", minv);
+	if (minv != default_minv)
+		appendPQExpBuffer(query, "    MINVALUE " INT64_FORMAT "\n", minv);
 	else
 		appendPQExpBufferStr(query, "    NO MINVALUE\n");
 
-	if (maxv)
-		appendPQExpBuffer(query, "    MAXVALUE %s\n", maxv);
+	if (maxv != default_maxv)
+		appendPQExpBuffer(query, "    MAXVALUE " INT64_FORMAT "\n", maxv);
 	else
 		appendPQExpBufferStr(query, "    NO MAXVALUE\n");
 
 	appendPQExpBuffer(query,
-					  "    CACHE %s%s",
+					  "    CACHE " INT64_FORMAT "%s",
 					  cache, (cycled ? "\n    CYCLE" : ""));
 
 	if (tbinfo->is_identity_sequence)
@@ -17448,8 +17464,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 					 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
 					 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 	destroyPQExpBuffer(delqry);
 	free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..974e21155a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
 SeqScanState
 SeqTable
 SeqTableData
+SeqType
 SerCommitSeqNo
 SerialControl
 SerialIOData
-- 
2.39.3 (Apple Git-146)

From 6f0ce8705acf916163f1272bd9e546e683faaa11 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v5 2/4] cache sequence information

---
 src/bin/pg_dump/pg_dump.c        | 175 ++++++++++++++++++++++---------
 src/tools/pgindent/typedefs.list |   1 +
 2 files changed, 129 insertions(+), 47 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index afafd641fe..9d1707623b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -121,6 +121,18 @@ const char *const SeqTypeNames[] =
 StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
 				 "array length mismatch");
 
+typedef struct
+{
+	Oid			oid;			/* sequence OID */
+	SeqType		seqtype;		/* data type of sequence */
+	bool		cycled;			/* whether sequence cycles */
+	int64		minv;			/* minimum value */
+	int64		maxv;			/* maximum value */
+	int64		startv;			/* start value */
+	int64		incby;			/* increment value */
+	int64		cache;			/* cache size */
+} SequenceItem;
+
 typedef enum OidOptions
 {
 	zeroIsError = 1,
@@ -190,6 +202,10 @@ static int	nseclabels = 0;
 static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
 static int	nbinaryUpgradeClassOids = 0;
 
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int	nsequences = 0;
+
 /*
  * The default number of rows per INSERT when
  * --inserts is specified without --rows-per-insert
@@ -287,6 +303,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
 static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
 static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
 static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
 static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -1009,6 +1026,9 @@ main(int argc, char **argv)
 	if (dopt.binary_upgrade)
 		collectBinaryUpgradeClassOids(fout);
 
+	/* Collect sequence information. */
+	collectSequences(fout);
+
 	/* Lastly, create dummy objects to represent the section boundaries */
 	boundaryObjs = createBoundaryObjects();
 
@@ -17218,6 +17238,65 @@ parse_sequence_type(const char *name)
 	pg_fatal("unrecognized sequence type: %s", name);
 }
 
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+	SequenceItem v1 = *((const SequenceItem *) p1);
+	SequenceItem v2 = *((const SequenceItem *) p2);
+
+	return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information.  This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+	PGresult   *res;
+	const char *query;
+
+	/*
+	 * Before Postgres 10, sequence metadata is in the sequence itself.  We
+	 * could likely make use of the sorted table with some extra effort, but
+	 * for now it seems unlikely to be worth it.
+	 */
+	if (fout->remoteVersion < 100000)
+		return;
+	else
+		query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+			"seqstart, seqincrement, "
+			"seqmax, seqmin, "
+			"seqcache, seqcycle "
+			"FROM pg_catalog.pg_sequence "
+			"ORDER BY seqrelid";
+
+	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+	nsequences = PQntuples(res);
+	sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+	for (int i = 0; i < nsequences; i++)
+	{
+		sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+		sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
+		sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+		sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+		sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+	}
+
+	PQclear(res);
+}
+
 /*
  * dumpSequence
  *	  write the declaration (not data) of one user-defined sequence
@@ -17226,17 +17305,10 @@ static void
 dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
-	PGresult   *res;
-	SeqType		seqtype;
-	bool		cycled;
+	SequenceItem *seq;
 	bool		is_ascending;
 	int64		default_minv,
-				default_maxv,
-				minv,
-				maxv,
-				startv,
-				incby,
-				cache;
+				default_maxv;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	char	   *qseqname;
@@ -17244,19 +17316,25 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 
 	qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
 
+	/*
+	 * For versions >= 10, the sequence information is gathered in a sorted
+	 * table before any calls to dumpSequence().  See collectSequences() for
+	 * more information.
+	 */
 	if (fout->remoteVersion >= 100000)
 	{
-		appendPQExpBuffer(query,
-						  "SELECT format_type(seqtypid, NULL), "
-						  "seqstart, seqincrement, "
-						  "seqmax, seqmin, "
-						  "seqcache, seqcycle "
-						  "FROM pg_catalog.pg_sequence "
-						  "WHERE seqrelid = '%u'::oid",
-						  tbinfo->dobj.catId.oid);
+		SequenceItem key = {0};
+
+		Assert(sequences);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		seq = bsearch(&key, sequences, nsequences,
+					  sizeof(SequenceItem), SequenceItemCmp);
 	}
 	else
 	{
+		PGresult   *res;
+
 		/*
 		 * Before PostgreSQL 10, sequence metadata is in the sequence itself.
 		 *
@@ -17268,46 +17346,47 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  "start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
 						  fmtQualifiedDumpable(tbinfo));
-	}
-
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
-	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
-	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
-	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
-	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
-	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
-	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		seq = pg_malloc0(sizeof(SequenceItem));
+		seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+		seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+		seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+		seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+		seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+		seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+		seq->cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
-	PQclear(res);
+		PQclear(res);
+	}
 
 	/* Calculate default limits for a sequence of this type */
-	is_ascending = (incby >= 0);
-	if (seqtype == SEQTYPE_SMALLINT)
+	is_ascending = (seq->incby >= 0);
+	if (seq->seqtype == SEQTYPE_SMALLINT)
 	{
 		default_minv = is_ascending ? 1 : PG_INT16_MIN;
 		default_maxv = is_ascending ? PG_INT16_MAX : -1;
 	}
-	else if (seqtype == SEQTYPE_INTEGER)
+	else if (seq->seqtype == SEQTYPE_INTEGER)
 	{
 		default_minv = is_ascending ? 1 : PG_INT32_MIN;
 		default_maxv = is_ascending ? PG_INT32_MAX : -1;
 	}
-	else if (seqtype == SEQTYPE_BIGINT)
+	else if (seq->seqtype == SEQTYPE_BIGINT)
 	{
 		default_minv = is_ascending ? 1 : PG_INT64_MIN;
 		default_maxv = is_ascending ? PG_INT64_MAX : -1;
 	}
 	else
 	{
-		pg_fatal("unrecognized sequence type: %d", seqtype);
+		pg_fatal("unrecognized sequence type: %d", seq->seqtype);
 		default_minv = default_maxv = 0;	/* keep compiler quiet */
 	}
 
@@ -17358,27 +17437,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  "UNLOGGED " : "",
 						  fmtQualifiedDumpable(tbinfo));
 
-		if (seqtype != SEQTYPE_BIGINT)
-			appendPQExpBuffer(query, "    AS %s\n", SeqTypeNames[seqtype]);
+		if (seq->seqtype != SEQTYPE_BIGINT)
+			appendPQExpBuffer(query, "    AS %s\n", SeqTypeNames[seq->seqtype]);
 	}
 
-	appendPQExpBuffer(query, "    START WITH " INT64_FORMAT "\n", startv);
+	appendPQExpBuffer(query, "    START WITH " INT64_FORMAT "\n", seq->startv);
 
-	appendPQExpBuffer(query, "    INCREMENT BY " INT64_FORMAT "\n", incby);
+	appendPQExpBuffer(query, "    INCREMENT BY " INT64_FORMAT "\n", seq->incby);
 
-	if (minv != default_minv)
-		appendPQExpBuffer(query, "    MINVALUE " INT64_FORMAT "\n", minv);
+	if (seq->minv != default_minv)
+		appendPQExpBuffer(query, "    MINVALUE " INT64_FORMAT "\n", seq->minv);
 	else
 		appendPQExpBufferStr(query, "    NO MINVALUE\n");
 
-	if (maxv != default_maxv)
-		appendPQExpBuffer(query, "    MAXVALUE " INT64_FORMAT "\n", maxv);
+	if (seq->maxv != default_maxv)
+		appendPQExpBuffer(query, "    MAXVALUE " INT64_FORMAT "\n", seq->maxv);
 	else
 		appendPQExpBufferStr(query, "    NO MAXVALUE\n");
 
 	appendPQExpBuffer(query,
 					  "    CACHE " INT64_FORMAT "%s",
-					  cache, (cycled ? "\n    CYCLE" : ""));
+					  seq->cache, (seq->cycled ? "\n    CYCLE" : ""));
 
 	if (tbinfo->is_identity_sequence)
 	{
@@ -17464,6 +17543,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 					 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
 					 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
 
+	if (fout->remoteVersion < 100000)
+		pg_free(seq);
 	destroyPQExpBuffer(query);
 	destroyPQExpBuffer(delqry);
 	free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 974e21155a..e1a46b5bf5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2573,6 +2573,7 @@ SeqScanState
 SeqTable
 SeqTableData
 SeqType
+SequenceItem
 SerCommitSeqNo
 SerialControl
 SerialIOData
-- 
2.39.3 (Apple Git-146)

From c74861298d44ab37f6b8bf1dd864614b8131dc20 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v5 3/4] introduce pg_sequence_read_tuple

XXX: NEEDS CATVERSION BUMP
---
 src/backend/commands/sequence.c        | 62 ++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat        |  6 +++
 src/test/regress/expected/sequence.out |  7 +++
 src/test/regress/sql/sequence.sql      |  3 ++
 4 files changed, 78 insertions(+)

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..364097c457 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 	return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
 }
 
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	SeqTable	elm;
+	Relation	seqrel;
+	Datum		values[SEQ_COL_LASTCOL];
+	bool		isnull[SEQ_COL_LASTCOL];
+	TupleDesc	resultTupleDesc;
+	HeapTuple	resultHeapTuple;
+	Datum		result;
+
+	resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+					   BOOLOID, -1, 0);
+	resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+	init_sequence(relid, &elm, &seqrel);
+
+	/*
+	 * Return all NULLs for sequences for which we lack privileges, other
+	 * sessions' temporary sequences, and unlogged sequences on standbys.
+	 */
+	if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
+		!RELATION_IS_OTHER_TEMP(seqrel) &&
+		(RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+	{
+		Buffer		buf;
+		HeapTupleData seqtuple;
+		Form_pg_sequence_data seq;
+
+		seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+		memset(isnull, false, sizeof(isnull));
+		values[0] = Int64GetDatum(seq->last_value);
+		values[1] = Int64GetDatum(seq->log_cnt);
+		values[2] = BoolGetDatum(seq->is_called);
+
+		UnlockReleaseBuffer(buf);
+	}
+	else
+		memset(isnull, true, sizeof(isnull));
+
+	sequence_close(seqrel, NoLock);
+
+	resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+	result = HeapTupleGetDatum(resultHeapTuple);
+	PG_RETURN_DATUM(result);
+}
+
+
 /*
  * Return the last value from the sequence
  *
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
   proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
   prorettype => 'int8', proargtypes => 'regclass',
   prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+  proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+  prorettype => 'record', proargtypes => 'regclass',
+  proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+  proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+  prosrc => 'pg_sequence_read_tuple' },
 
 { oid => '275', descr => 'return the next oid for a system table',
   proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
        3
 (1 row)
 
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called 
+------------+---------+-----------
+         10 |      32 | t
+(1 row)
+
 DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
 SELECT nextval('test_seq1');
 SELECT nextval('test_seq1');
 
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
 DROP SEQUENCE test_seq1;
-- 
2.39.3 (Apple Git-146)

From 66c8711d0a14db4e418bbf136437568f6b48ce40 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v5 4/4] cache sequence data

---
 src/bin/pg_dump/pg_dump.c | 81 ++++++++++++++++++++++++++++++---------
 1 file changed, 63 insertions(+), 18 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9d1707623b..2c5c614abd 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -131,6 +131,8 @@ typedef struct
 	int64		startv;			/* start value */
 	int64		incby;			/* increment value */
 	int64		cache;			/* cache size */
+	int64		last_value;		/* last value of sequence */
+	bool		is_called;		/* whether nextval advances before returning */
 } SequenceItem;
 
 typedef enum OidOptions
@@ -17266,16 +17268,30 @@ collectSequences(Archive *fout)
 	 * Before Postgres 10, sequence metadata is in the sequence itself.  We
 	 * could likely make use of the sorted table with some extra effort, but
 	 * for now it seems unlikely to be worth it.
+	 *
+	 * Since version 18, we can gather the sequence data in this query with
+	 * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
 	 */
 	if (fout->remoteVersion < 100000)
 		return;
-	else
+	else if (fout->remoteVersion < 180000 ||
+			 (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
 		query = "SELECT seqrelid, format_type(seqtypid, NULL), "
 			"seqstart, seqincrement, "
 			"seqmax, seqmin, "
-			"seqcache, seqcycle "
+			"seqcache, seqcycle, "
+			"NULL, 'f' "
 			"FROM pg_catalog.pg_sequence "
 			"ORDER BY seqrelid";
+	else
+		query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+			"seqstart, seqincrement, "
+			"seqmax, seqmin, "
+			"seqcache, seqcycle, "
+			"last_value, is_called "
+			"FROM pg_catalog.pg_sequence, "
+			"pg_sequence_read_tuple(seqrelid) "
+			"ORDER BY seqrelid;";
 
 	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
 
@@ -17292,6 +17308,8 @@ collectSequences(Archive *fout)
 		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
 		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
 		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+		sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+		sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
 	}
 
 	PQclear(res);
@@ -17558,30 +17576,59 @@ static void
 dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 {
 	TableInfo  *tbinfo = tdinfo->tdtable;
-	PGresult   *res;
-	char	   *last;
+	int64		last;
 	bool		called;
 	PQExpBuffer query = createPQExpBuffer();
 
-	appendPQExpBuffer(query,
-					  "SELECT last_value, is_called FROM %s",
-					  fmtQualifiedDumpable(tbinfo));
+	/*
+	 * For versions >= 18, the sequence information is gathered in the sorted
+	 * array before any calls to dumpSequenceData().  See collectSequences()
+	 * for more information.
+	 *
+	 * For older versions, we have to query the sequence relations
+	 * individually.
+	 */
+	if (fout->remoteVersion < 180000)
+	{
+		PGresult   *res;
 
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+		appendPQExpBuffer(query,
+						  "SELECT last_value, is_called FROM %s",
+						  fmtQualifiedDumpable(tbinfo));
 
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	last = PQgetvalue(res, 0, 0);
-	called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+		called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+		PQclear(res);
+	}
+	else
+	{
+		SequenceItem key = {0};
+		SequenceItem *entry;
+
+		Assert(sequences);
+		Assert(tbinfo->dobj.catId.oid);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		entry = bsearch(&key, sequences, nsequences,
+						sizeof(SequenceItem), SequenceItemCmp);
+
+		last = entry->last_value;
+		called = entry->is_called;
+	}
 
 	resetPQExpBuffer(query);
 	appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
 	appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
-	appendPQExpBuffer(query, ", %s, %s);\n",
+	appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
 					  last, (called ? "true" : "false"));
 
 	if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17595,8 +17642,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 								  .deps = &(tbinfo->dobj.dumpId),
 								  .nDeps = 1));
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 }
 
-- 
2.39.3 (Apple Git-146)

From 139826d3b9290d547ae9b3446ad455ba713211e0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v6 1/4] parse sequence information

---
 src/bin/pg_dump/pg_dump.c        | 103 ++++++++++++++++++-------------
 src/tools/pgindent/typedefs.list |   1 +
 2 files changed, 60 insertions(+), 44 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..74e22e5e1e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,23 @@ typedef struct
 	RelFileNumber toast_index_relfilenumber;	/* toast table index filenode */
 } BinaryUpgradeClassOidItem;
 
+typedef enum SeqType
+{
+	SEQTYPE_SMALLINT,
+	SEQTYPE_INTEGER,
+	SEQTYPE_BIGINT,
+} SeqType;
+
+const char *const SeqTypeNames[] =
+{
+	[SEQTYPE_SMALLINT] = "smallint",
+	[SEQTYPE_INTEGER] = "integer",
+	[SEQTYPE_BIGINT] = "bigint",
+};
+
+StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
+				 "array length mismatch");
+
 typedef enum OidOptions
 {
 	zeroIsError = 1,
@@ -17189,6 +17206,19 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
 	free(qtabname);
 }
 
+static inline SeqType
+parse_sequence_type(const char *name)
+{
+	for (int i = 0; i < lengthof(SeqTypeNames); i++)
+	{
+		if (strcmp(SeqTypeNames[i], name) == 0)
+			return (SeqType) i;
+	}
+
+	pg_fatal("unrecognized sequence type: %s", name);
+	return (SeqType) 0;			/* keep compiler quiet */
+}
+
 /*
  * dumpSequence
  *	  write the declaration (not data) of one user-defined sequence
@@ -17198,18 +17228,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
 	PGresult   *res;
-	char	   *startv,
-			   *incby,
-			   *maxv,
-			   *minv,
-			   *cache,
-			   *seqtype;
+	SeqType		seqtype;
 	bool		cycled;
 	bool		is_ascending;
 	int64		default_minv,
-				default_maxv;
-	char		bufm[32],
-				bufx[32];
+				default_maxv,
+				minv,
+				maxv,
+				startv,
+				incby,
+				cache;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	char	   *qseqname;
@@ -17251,50 +17279,39 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  PQntuples(res)),
 				 tbinfo->dobj.name, PQntuples(res));
 
-	seqtype = PQgetvalue(res, 0, 0);
-	startv = PQgetvalue(res, 0, 1);
-	incby = PQgetvalue(res, 0, 2);
-	maxv = PQgetvalue(res, 0, 3);
-	minv = PQgetvalue(res, 0, 4);
-	cache = PQgetvalue(res, 0, 5);
+	seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
 	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
+	PQclear(res);
+
 	/* Calculate default limits for a sequence of this type */
-	is_ascending = (incby[0] != '-');
-	if (strcmp(seqtype, "smallint") == 0)
+	is_ascending = (incby >= 0);
+	if (seqtype == SEQTYPE_SMALLINT)
 	{
 		default_minv = is_ascending ? 1 : PG_INT16_MIN;
 		default_maxv = is_ascending ? PG_INT16_MAX : -1;
 	}
-	else if (strcmp(seqtype, "integer") == 0)
+	else if (seqtype == SEQTYPE_INTEGER)
 	{
 		default_minv = is_ascending ? 1 : PG_INT32_MIN;
 		default_maxv = is_ascending ? PG_INT32_MAX : -1;
 	}
-	else if (strcmp(seqtype, "bigint") == 0)
+	else if (seqtype == SEQTYPE_BIGINT)
 	{
 		default_minv = is_ascending ? 1 : PG_INT64_MIN;
 		default_maxv = is_ascending ? PG_INT64_MAX : -1;
 	}
 	else
 	{
-		pg_fatal("unrecognized sequence type: %s", seqtype);
+		pg_fatal("unrecognized sequence type: %d", seqtype);
 		default_minv = default_maxv = 0;	/* keep compiler quiet */
 	}
 
-	/*
-	 * 64-bit strtol() isn't very portable, so convert the limits to strings
-	 * and compare that way.
-	 */
-	snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
-	snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
-	/* Don't print minv/maxv if they match the respective default limit */
-	if (strcmp(minv, bufm) == 0)
-		minv = NULL;
-	if (strcmp(maxv, bufx) == 0)
-		maxv = NULL;
-
 	/*
 	 * Identity sequences are not to be dropped separately.
 	 */
@@ -17342,26 +17359,26 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  "UNLOGGED " : "",
 						  fmtQualifiedDumpable(tbinfo));
 
-		if (strcmp(seqtype, "bigint") != 0)
-			appendPQExpBuffer(query, "    AS %s\n", seqtype);
+		if (seqtype != SEQTYPE_BIGINT)
+			appendPQExpBuffer(query, "    AS %s\n", SeqTypeNames[seqtype]);
 	}
 
-	appendPQExpBuffer(query, "    START WITH %s\n", startv);
+	appendPQExpBuffer(query, "    START WITH " INT64_FORMAT "\n", startv);
 
-	appendPQExpBuffer(query, "    INCREMENT BY %s\n", incby);
+	appendPQExpBuffer(query, "    INCREMENT BY " INT64_FORMAT "\n", incby);
 
-	if (minv)
-		appendPQExpBuffer(query, "    MINVALUE %s\n", minv);
+	if (minv != default_minv)
+		appendPQExpBuffer(query, "    MINVALUE " INT64_FORMAT "\n", minv);
 	else
 		appendPQExpBufferStr(query, "    NO MINVALUE\n");
 
-	if (maxv)
-		appendPQExpBuffer(query, "    MAXVALUE %s\n", maxv);
+	if (maxv != default_maxv)
+		appendPQExpBuffer(query, "    MAXVALUE " INT64_FORMAT "\n", maxv);
 	else
 		appendPQExpBufferStr(query, "    NO MAXVALUE\n");
 
 	appendPQExpBuffer(query,
-					  "    CACHE %s%s",
+					  "    CACHE " INT64_FORMAT "%s",
 					  cache, (cycled ? "\n    CYCLE" : ""));
 
 	if (tbinfo->is_identity_sequence)
@@ -17448,8 +17465,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 					 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
 					 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 	destroyPQExpBuffer(delqry);
 	free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..974e21155a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
 SeqScanState
 SeqTable
 SeqTableData
+SeqType
 SerCommitSeqNo
 SerialControl
 SerialIOData
-- 
2.39.3 (Apple Git-146)

From 7bf81a53af63c993478bb47fe347deefd1145bb4 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v6 2/4] cache sequence information

---
 src/bin/pg_dump/pg_dump.c        | 175 ++++++++++++++++++++++---------
 src/tools/pgindent/typedefs.list |   1 +
 2 files changed, 129 insertions(+), 47 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 74e22e5e1e..8c42fd762d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -121,6 +121,18 @@ const char *const SeqTypeNames[] =
 StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
 				 "array length mismatch");
 
+typedef struct
+{
+	Oid			oid;			/* sequence OID */
+	SeqType		seqtype;		/* data type of sequence */
+	bool		cycled;			/* whether sequence cycles */
+	int64		minv;			/* minimum value */
+	int64		maxv;			/* maximum value */
+	int64		startv;			/* start value */
+	int64		incby;			/* increment value */
+	int64		cache;			/* cache size */
+} SequenceItem;
+
 typedef enum OidOptions
 {
 	zeroIsError = 1,
@@ -190,6 +202,10 @@ static int	nseclabels = 0;
 static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
 static int	nbinaryUpgradeClassOids = 0;
 
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int	nsequences = 0;
+
 /*
  * The default number of rows per INSERT when
  * --inserts is specified without --rows-per-insert
@@ -287,6 +303,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
 static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
 static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
 static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
 static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
 static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -1009,6 +1026,9 @@ main(int argc, char **argv)
 	if (dopt.binary_upgrade)
 		collectBinaryUpgradeClassOids(fout);
 
+	/* Collect sequence information. */
+	collectSequences(fout);
+
 	/* Lastly, create dummy objects to represent the section boundaries */
 	boundaryObjs = createBoundaryObjects();
 
@@ -17219,6 +17239,65 @@ parse_sequence_type(const char *name)
 	return (SeqType) 0;			/* keep compiler quiet */
 }
 
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+	SequenceItem v1 = *((const SequenceItem *) p1);
+	SequenceItem v2 = *((const SequenceItem *) p2);
+
+	return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information.  This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+	PGresult   *res;
+	const char *query;
+
+	/*
+	 * Before Postgres 10, sequence metadata is in the sequence itself.  We
+	 * could likely make use of the sorted table with some extra effort, but
+	 * for now it seems unlikely to be worth it.
+	 */
+	if (fout->remoteVersion < 100000)
+		return;
+	else
+		query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+			"seqstart, seqincrement, "
+			"seqmax, seqmin, "
+			"seqcache, seqcycle "
+			"FROM pg_catalog.pg_sequence "
+			"ORDER BY seqrelid";
+
+	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+	nsequences = PQntuples(res);
+	sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+	for (int i = 0; i < nsequences; i++)
+	{
+		sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+		sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
+		sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+		sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+		sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+	}
+
+	PQclear(res);
+}
+
 /*
  * dumpSequence
  *	  write the declaration (not data) of one user-defined sequence
@@ -17227,17 +17306,10 @@ static void
 dumpSequence(Archive *fout, const TableInfo *tbinfo)
 {
 	DumpOptions *dopt = fout->dopt;
-	PGresult   *res;
-	SeqType		seqtype;
-	bool		cycled;
+	SequenceItem *seq;
 	bool		is_ascending;
 	int64		default_minv,
-				default_maxv,
-				minv,
-				maxv,
-				startv,
-				incby,
-				cache;
+				default_maxv;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	char	   *qseqname;
@@ -17245,19 +17317,25 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 
 	qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
 
+	/*
+	 * For versions >= 10, the sequence information is gathered in a sorted
+	 * table before any calls to dumpSequence().  See collectSequences() for
+	 * more information.
+	 */
 	if (fout->remoteVersion >= 100000)
 	{
-		appendPQExpBuffer(query,
-						  "SELECT format_type(seqtypid, NULL), "
-						  "seqstart, seqincrement, "
-						  "seqmax, seqmin, "
-						  "seqcache, seqcycle "
-						  "FROM pg_catalog.pg_sequence "
-						  "WHERE seqrelid = '%u'::oid",
-						  tbinfo->dobj.catId.oid);
+		SequenceItem key = {0};
+
+		Assert(sequences);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		seq = bsearch(&key, sequences, nsequences,
+					  sizeof(SequenceItem), SequenceItemCmp);
 	}
 	else
 	{
+		PGresult   *res;
+
 		/*
 		 * Before PostgreSQL 10, sequence metadata is in the sequence itself.
 		 *
@@ -17269,46 +17347,47 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  "start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
 						  fmtQualifiedDumpable(tbinfo));
-	}
-
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
-	startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
-	incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
-	maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
-	minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
-	cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
-	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		seq = pg_malloc0(sizeof(SequenceItem));
+		seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+		seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+		seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+		seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+		seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+		seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+		seq->cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
 
-	PQclear(res);
+		PQclear(res);
+	}
 
 	/* Calculate default limits for a sequence of this type */
-	is_ascending = (incby >= 0);
-	if (seqtype == SEQTYPE_SMALLINT)
+	is_ascending = (seq->incby >= 0);
+	if (seq->seqtype == SEQTYPE_SMALLINT)
 	{
 		default_minv = is_ascending ? 1 : PG_INT16_MIN;
 		default_maxv = is_ascending ? PG_INT16_MAX : -1;
 	}
-	else if (seqtype == SEQTYPE_INTEGER)
+	else if (seq->seqtype == SEQTYPE_INTEGER)
 	{
 		default_minv = is_ascending ? 1 : PG_INT32_MIN;
 		default_maxv = is_ascending ? PG_INT32_MAX : -1;
 	}
-	else if (seqtype == SEQTYPE_BIGINT)
+	else if (seq->seqtype == SEQTYPE_BIGINT)
 	{
 		default_minv = is_ascending ? 1 : PG_INT64_MIN;
 		default_maxv = is_ascending ? PG_INT64_MAX : -1;
 	}
 	else
 	{
-		pg_fatal("unrecognized sequence type: %d", seqtype);
+		pg_fatal("unrecognized sequence type: %d", seq->seqtype);
 		default_minv = default_maxv = 0;	/* keep compiler quiet */
 	}
 
@@ -17359,27 +17438,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 						  "UNLOGGED " : "",
 						  fmtQualifiedDumpable(tbinfo));
 
-		if (seqtype != SEQTYPE_BIGINT)
-			appendPQExpBuffer(query, "    AS %s\n", SeqTypeNames[seqtype]);
+		if (seq->seqtype != SEQTYPE_BIGINT)
+			appendPQExpBuffer(query, "    AS %s\n", SeqTypeNames[seq->seqtype]);
 	}
 
-	appendPQExpBuffer(query, "    START WITH " INT64_FORMAT "\n", startv);
+	appendPQExpBuffer(query, "    START WITH " INT64_FORMAT "\n", seq->startv);
 
-	appendPQExpBuffer(query, "    INCREMENT BY " INT64_FORMAT "\n", incby);
+	appendPQExpBuffer(query, "    INCREMENT BY " INT64_FORMAT "\n", seq->incby);
 
-	if (minv != default_minv)
-		appendPQExpBuffer(query, "    MINVALUE " INT64_FORMAT "\n", minv);
+	if (seq->minv != default_minv)
+		appendPQExpBuffer(query, "    MINVALUE " INT64_FORMAT "\n", seq->minv);
 	else
 		appendPQExpBufferStr(query, "    NO MINVALUE\n");
 
-	if (maxv != default_maxv)
-		appendPQExpBuffer(query, "    MAXVALUE " INT64_FORMAT "\n", maxv);
+	if (seq->maxv != default_maxv)
+		appendPQExpBuffer(query, "    MAXVALUE " INT64_FORMAT "\n", seq->maxv);
 	else
 		appendPQExpBufferStr(query, "    NO MAXVALUE\n");
 
 	appendPQExpBuffer(query,
 					  "    CACHE " INT64_FORMAT "%s",
-					  cache, (cycled ? "\n    CYCLE" : ""));
+					  seq->cache, (seq->cycled ? "\n    CYCLE" : ""));
 
 	if (tbinfo->is_identity_sequence)
 	{
@@ -17465,6 +17544,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 					 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
 					 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
 
+	if (fout->remoteVersion < 100000)
+		pg_free(seq);
 	destroyPQExpBuffer(query);
 	destroyPQExpBuffer(delqry);
 	free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 974e21155a..e1a46b5bf5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2573,6 +2573,7 @@ SeqScanState
 SeqTable
 SeqTableData
 SeqType
+SequenceItem
 SerCommitSeqNo
 SerialControl
 SerialIOData
-- 
2.39.3 (Apple Git-146)

From e83ca9e2312e31ccf4430e8fd24744eff3d6fe75 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v6 3/4] introduce pg_sequence_read_tuple

XXX: NEEDS CATVERSION BUMP
---
 src/backend/commands/sequence.c        | 62 ++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat        |  6 +++
 src/test/regress/expected/sequence.out |  7 +++
 src/test/regress/sql/sequence.sql      |  3 ++
 4 files changed, 78 insertions(+)

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..364097c457 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 	return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
 }
 
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	SeqTable	elm;
+	Relation	seqrel;
+	Datum		values[SEQ_COL_LASTCOL];
+	bool		isnull[SEQ_COL_LASTCOL];
+	TupleDesc	resultTupleDesc;
+	HeapTuple	resultHeapTuple;
+	Datum		result;
+
+	resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+					   BOOLOID, -1, 0);
+	resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+	init_sequence(relid, &elm, &seqrel);
+
+	/*
+	 * Return all NULLs for sequences for which we lack privileges, other
+	 * sessions' temporary sequences, and unlogged sequences on standbys.
+	 */
+	if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
+		!RELATION_IS_OTHER_TEMP(seqrel) &&
+		(RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+	{
+		Buffer		buf;
+		HeapTupleData seqtuple;
+		Form_pg_sequence_data seq;
+
+		seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+		memset(isnull, false, sizeof(isnull));
+		values[0] = Int64GetDatum(seq->last_value);
+		values[1] = Int64GetDatum(seq->log_cnt);
+		values[2] = BoolGetDatum(seq->is_called);
+
+		UnlockReleaseBuffer(buf);
+	}
+	else
+		memset(isnull, true, sizeof(isnull));
+
+	sequence_close(seqrel, NoLock);
+
+	resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+	result = HeapTupleGetDatum(resultHeapTuple);
+	PG_RETURN_DATUM(result);
+}
+
+
 /*
  * Return the last value from the sequence
  *
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
   proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
   prorettype => 'int8', proargtypes => 'regclass',
   prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+  proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+  prorettype => 'record', proargtypes => 'regclass',
+  proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+  proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+  prosrc => 'pg_sequence_read_tuple' },
 
 { oid => '275', descr => 'return the next oid for a system table',
   proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
        3
 (1 row)
 
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called 
+------------+---------+-----------
+         10 |      32 | t
+(1 row)
+
 DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
 SELECT nextval('test_seq1');
 SELECT nextval('test_seq1');
 
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
 DROP SEQUENCE test_seq1;
-- 
2.39.3 (Apple Git-146)

From c339097c8a6be238798d14ec08d157d818bdf3fc Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v6 4/4] cache sequence data

---
 src/bin/pg_dump/pg_dump.c | 81 ++++++++++++++++++++++++++++++---------
 1 file changed, 63 insertions(+), 18 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8c42fd762d..78f6f608bc 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -131,6 +131,8 @@ typedef struct
 	int64		startv;			/* start value */
 	int64		incby;			/* increment value */
 	int64		cache;			/* cache size */
+	int64		last_value;		/* last value of sequence */
+	bool		is_called;		/* whether nextval advances before returning */
 } SequenceItem;
 
 typedef enum OidOptions
@@ -17267,16 +17269,30 @@ collectSequences(Archive *fout)
 	 * Before Postgres 10, sequence metadata is in the sequence itself.  We
 	 * could likely make use of the sorted table with some extra effort, but
 	 * for now it seems unlikely to be worth it.
+	 *
+	 * Since version 18, we can gather the sequence data in this query with
+	 * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
 	 */
 	if (fout->remoteVersion < 100000)
 		return;
-	else
+	else if (fout->remoteVersion < 180000 ||
+			 (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
 		query = "SELECT seqrelid, format_type(seqtypid, NULL), "
 			"seqstart, seqincrement, "
 			"seqmax, seqmin, "
-			"seqcache, seqcycle "
+			"seqcache, seqcycle, "
+			"NULL, 'f' "
 			"FROM pg_catalog.pg_sequence "
 			"ORDER BY seqrelid";
+	else
+		query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+			"seqstart, seqincrement, "
+			"seqmax, seqmin, "
+			"seqcache, seqcycle, "
+			"last_value, is_called "
+			"FROM pg_catalog.pg_sequence, "
+			"pg_sequence_read_tuple(seqrelid) "
+			"ORDER BY seqrelid;";
 
 	res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
 
@@ -17293,6 +17309,8 @@ collectSequences(Archive *fout)
 		sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
 		sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
 		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+		sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+		sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
 	}
 
 	PQclear(res);
@@ -17559,30 +17577,59 @@ static void
 dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 {
 	TableInfo  *tbinfo = tdinfo->tdtable;
-	PGresult   *res;
-	char	   *last;
+	int64		last;
 	bool		called;
 	PQExpBuffer query = createPQExpBuffer();
 
-	appendPQExpBuffer(query,
-					  "SELECT last_value, is_called FROM %s",
-					  fmtQualifiedDumpable(tbinfo));
+	/*
+	 * For versions >= 18, the sequence information is gathered in the sorted
+	 * array before any calls to dumpSequenceData().  See collectSequences()
+	 * for more information.
+	 *
+	 * For older versions, we have to query the sequence relations
+	 * individually.
+	 */
+	if (fout->remoteVersion < 180000)
+	{
+		PGresult   *res;
 
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+		appendPQExpBuffer(query,
+						  "SELECT last_value, is_called FROM %s",
+						  fmtQualifiedDumpable(tbinfo));
 
-	if (PQntuples(res) != 1)
-		pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
-						  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
-						  PQntuples(res)),
-				 tbinfo->dobj.name, PQntuples(res));
+		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	last = PQgetvalue(res, 0, 0);
-	called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+		if (PQntuples(res) != 1)
+			pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+							  "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+							  PQntuples(res)),
+					 tbinfo->dobj.name, PQntuples(res));
+
+		last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+		called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+		PQclear(res);
+	}
+	else
+	{
+		SequenceItem key = {0};
+		SequenceItem *entry;
+
+		Assert(sequences);
+		Assert(tbinfo->dobj.catId.oid);
+
+		key.oid = tbinfo->dobj.catId.oid;
+		entry = bsearch(&key, sequences, nsequences,
+						sizeof(SequenceItem), SequenceItemCmp);
+
+		last = entry->last_value;
+		called = entry->is_called;
+	}
 
 	resetPQExpBuffer(query);
 	appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
 	appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
-	appendPQExpBuffer(query, ", %s, %s);\n",
+	appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
 					  last, (called ? "true" : "false"));
 
 	if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17596,8 +17643,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 								  .deps = &(tbinfo->dobj.dumpId),
 								  .nDeps = 1));
 
-	PQclear(res);
-
 	destroyPQExpBuffer(query);
 }
 
-- 
2.39.3 (Apple Git-146)

From aeabbd9b6e336d086941eb89f2b4983b58c5881e Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 8 Jan 2026 11:29:59 -0600
Subject: [PATCH v1 1/1] pg_dump: fix use of pg_get_sequence_data

---
 src/backend/access/sequence/sequence.c | 20 ++++++++++++++++++++
 src/backend/commands/sequence.c        | 14 ++++++++------
 src/bin/pg_dump/pg_dump.c              | 10 ++++++++++
 src/include/access/sequence.h          |  1 +
 4 files changed, 39 insertions(+), 6 deletions(-)

diff --git a/src/backend/access/sequence/sequence.c b/src/backend/access/sequence/sequence.c
index 106af1477e9..6f21f49a520 100644
--- a/src/backend/access/sequence/sequence.c
+++ b/src/backend/access/sequence/sequence.c
@@ -45,6 +45,26 @@ sequence_open(Oid relationId, LOCKMODE lockmode)
 	return r;
 }
 
+/* ----------------
+ *		try_sequence_open - open a sequence relation by relation OID
+ *
+ *		Same as sequence_open, except return NULL instead of failing if the
+ *		relation does not exist.
+ * ----------------
+ */
+Relation
+try_sequence_open(Oid relationId, LOCKMODE lockmode)
+{
+	Relation	r;
+
+	r = try_relation_open(relationId, lockmode);
+
+	if (r)
+		validate_relation_kind(r);
+
+	return r;
+}
+
 /* ----------------
  *		sequence_close - close a sequence
  *
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 904eeada5ab..e3202dfe951 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1794,7 +1794,6 @@ pg_get_sequence_data(PG_FUNCTION_ARGS)
 {
 #define PG_GET_SEQUENCE_DATA_COLS	3
 	Oid			relid = PG_GETARG_OID(0);
-	SeqTable	elm;
 	Relation	seqrel;
 	Datum		values[PG_GET_SEQUENCE_DATA_COLS] = {0};
 	bool		isnull[PG_GET_SEQUENCE_DATA_COLS] = {0};
@@ -1811,13 +1810,15 @@ pg_get_sequence_data(PG_FUNCTION_ARGS)
 					   LSNOID, -1, 0);
 	resultTupleDesc = BlessTupleDesc(resultTupleDesc);
 
-	init_sequence(relid, &elm, &seqrel);
+	seqrel = try_sequence_open(relid, AccessShareLock);
 
 	/*
-	 * Return all NULLs for sequences for which we lack privileges, other
-	 * sessions' temporary sequences, and unlogged sequences on standbys.
+	 * Return all NULLs for missing sequences, sequences for which we lack
+	 * privileges, other sessions' temporary sequences, and unlogged sequences
+	 * on standbys.
 	 */
-	if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
+	if (seqrel &&
+		pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
 		!RELATION_IS_OTHER_TEMP(seqrel) &&
 		(RelationIsPermanent(seqrel) || !RecoveryInProgress()))
 	{
@@ -1838,7 +1839,8 @@ pg_get_sequence_data(PG_FUNCTION_ARGS)
 	else
 		memset(isnull, true, sizeof(isnull));
 
-	sequence_close(seqrel, NoLock);
+	if (seqrel)
+		sequence_close(seqrel, AccessShareLock);
 
 	resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
 	result = HeapTupleGetDatum(resultHeapTuple);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7df56d8b1b0..573fb0c06a1 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -137,6 +137,7 @@ typedef struct
 	int64		cache;			/* cache size */
 	int64		last_value;		/* last value of sequence */
 	bool		is_called;		/* whether nextval advances before returning */
+	bool		null_seqtuple;	/* did pg_get_sequence_data return nulls? */
 } SequenceItem;
 
 typedef enum OidOptions
@@ -18959,6 +18960,7 @@ collectSequences(Archive *fout)
 		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
 		sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
 		sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
+		sequences[i].null_seqtuple = (PQgetisnull(res, i, 8) || PQgetisnull(res, i, 9));
 	}
 
 	PQclear(res);
@@ -19230,6 +19232,10 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 	bool		called;
 	PQExpBuffer query = createPQExpBuffer();
 
+	/* needn't bother if not dumping sequence data */
+	if (!fout->dopt->dumpData && !fout->dopt->sequence_data)
+		return;
+
 	/*
 	 * For versions >= 18, the sequence information is gathered in the sorted
 	 * array before any calls to dumpSequenceData().  See collectSequences()
@@ -19271,6 +19277,10 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 		entry = bsearch(&key, sequences, nsequences,
 						sizeof(SequenceItem), SequenceItemCmp);
 
+		if (entry->null_seqtuple)
+			pg_fatal("failed to get data for sequence \"%s\"; user may lack privileges or sequence may have been dropped",
+					 tbinfo->dobj.name);
+
 		last = entry->last_value;
 		called = entry->is_called;
 	}
diff --git a/src/include/access/sequence.h b/src/include/access/sequence.h
index cbe93775b1b..42f199003b4 100644
--- a/src/include/access/sequence.h
+++ b/src/include/access/sequence.h
@@ -18,6 +18,7 @@
 #include "utils/relcache.h"
 
 extern Relation sequence_open(Oid relationId, LOCKMODE lockmode);
+extern Relation try_sequence_open(Oid relationId, LOCKMODE lockmode);
 extern void sequence_close(Relation relation, LOCKMODE lockmode);
 
 #endif							/* ACCESS_SEQUENCE_H */
-- 
2.39.5 (Apple Git-154)

From 68a9b7fb9714805819b040f19851601f1426c990 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 8 Jan 2026 11:29:59 -0600
Subject: [PATCH v2 1/1] pg_dump: fix use of pg_get_sequence_data

---
 src/backend/commands/sequence.c | 14 ++++++++------
 src/bin/pg_dump/pg_dump.c       | 10 ++++++++++
 2 files changed, 18 insertions(+), 6 deletions(-)

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 904eeada5ab..e1b808bbb60 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1794,7 +1794,6 @@ pg_get_sequence_data(PG_FUNCTION_ARGS)
 {
 #define PG_GET_SEQUENCE_DATA_COLS	3
 	Oid			relid = PG_GETARG_OID(0);
-	SeqTable	elm;
 	Relation	seqrel;
 	Datum		values[PG_GET_SEQUENCE_DATA_COLS] = {0};
 	bool		isnull[PG_GET_SEQUENCE_DATA_COLS] = {0};
@@ -1811,13 +1810,15 @@ pg_get_sequence_data(PG_FUNCTION_ARGS)
 					   LSNOID, -1, 0);
 	resultTupleDesc = BlessTupleDesc(resultTupleDesc);
 
-	init_sequence(relid, &elm, &seqrel);
+	seqrel = try_relation_open(relid, AccessShareLock);
 
 	/*
-	 * Return all NULLs for sequences for which we lack privileges, other
-	 * sessions' temporary sequences, and unlogged sequences on standbys.
+	 * Return all NULLs for missing sequences, sequences for which we lack
+	 * privileges, other sessions' temporary sequences, and unlogged sequences
+	 * on standbys.
 	 */
-	if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
+	if (seqrel && seqrel->rd_rel->relkind == RELKIND_SEQUENCE &&
+		pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
 		!RELATION_IS_OTHER_TEMP(seqrel) &&
 		(RelationIsPermanent(seqrel) || !RecoveryInProgress()))
 	{
@@ -1838,7 +1839,8 @@ pg_get_sequence_data(PG_FUNCTION_ARGS)
 	else
 		memset(isnull, true, sizeof(isnull));
 
-	sequence_close(seqrel, NoLock);
+	if (seqrel)
+		relation_close(seqrel, AccessShareLock);
 
 	resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
 	result = HeapTupleGetDatum(resultHeapTuple);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7df56d8b1b0..573fb0c06a1 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -137,6 +137,7 @@ typedef struct
 	int64		cache;			/* cache size */
 	int64		last_value;		/* last value of sequence */
 	bool		is_called;		/* whether nextval advances before returning */
+	bool		null_seqtuple;	/* did pg_get_sequence_data return nulls? */
 } SequenceItem;
 
 typedef enum OidOptions
@@ -18959,6 +18960,7 @@ collectSequences(Archive *fout)
 		sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
 		sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
 		sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
+		sequences[i].null_seqtuple = (PQgetisnull(res, i, 8) || PQgetisnull(res, i, 9));
 	}
 
 	PQclear(res);
@@ -19230,6 +19232,10 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 	bool		called;
 	PQExpBuffer query = createPQExpBuffer();
 
+	/* needn't bother if not dumping sequence data */
+	if (!fout->dopt->dumpData && !fout->dopt->sequence_data)
+		return;
+
 	/*
 	 * For versions >= 18, the sequence information is gathered in the sorted
 	 * array before any calls to dumpSequenceData().  See collectSequences()
@@ -19271,6 +19277,10 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
 		entry = bsearch(&key, sequences, nsequences,
 						sizeof(SequenceItem), SequenceItemCmp);
 
+		if (entry->null_seqtuple)
+			pg_fatal("failed to get data for sequence \"%s\"; user may lack privileges or sequence may have been dropped",
+					 tbinfo->dobj.name);
+
 		last = entry->last_value;
 		called = entry->is_called;
 	}
-- 
2.39.5 (Apple Git-154)