Date: Thu, 8 Jan 2026 11:36:38 -0600
From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paquier <michael@paquier.xyz>,
	Euler Taveira <euler@eulerto.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: improve performance of pg_dump with many sequences
Message-ID: <aV_rJv-JnMh6WWQw@nathan>
References: <3640163.1721275101@sss.pgh.pa.us>
 <ZpldUoP0XjHszExF@nathan>
 <Zpl7HywbF2qAkTVf@nathan>
 <ZqFJspSbieuTE4X0@nathan>
 <ZqpV5Sb7YWauwYnx@nathan>
 <2885944.1767029161@sss.pgh.pa.us>
 <aV7m7rTty4KAb-tF@nathan>
 <1862355.1767827628@sss.pgh.pa.us>
 <aV7r43VN-kBR6T8v@nathan>
 <1869482.1767831892@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="iyprHr6gT7UrymdC"
Content-Disposition: inline
In-Reply-To: <1869482.1767831892@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/aV_rJv-JnMh6WWQw%40nathan>
Precedence: bulk


--iyprHr6gT7UrymdC
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Wed, Jan 07, 2026 at 07:24:52PM -0500, Tom Lane wrote:
> Nathan Bossart <nathandbossart@gmail.com> writes:
>> On Wed, Jan 07, 2026 at 06:13:48PM -0500, Tom Lane wrote:
>>> That would be a fine argument were it not that collectSequences()
>>> tries to vacuum up the data for every sequence in the DB, whether
>>> the user has asked to dump them all or not.
> 
>> I meant that we could teach pg_dump to error in dumpSequenceData() if it
>> sees nulls for the sequence in question.
> 
> Ah, gotcha; I thought you were talking about changing
> pg_get_sequence_data() to throw an error instead of returning nulls.

Here is a patch that does this along with what you described upthread,
i.e., teaching pg_get_sequence_data to return nulls for missing sequences.
Apparently pg_dump still runs through dumpSequenceData() for schema-only
dumps, which is a problem for this patch.  I've taught it to immediately
return for schema-only dumps to evade this problem.  That seems like a win
for older versions, too, as they will no longer run useless queries.

I believe this helps the reporter's case, as their problem involves dumping
one schema while dropping another, which v18 indeed makes worse because (as
you mentioned) we gather data for all sequences in the database.

-- 
nathan

--iyprHr6gT7UrymdC
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v1-0001-pg_dump-fix-use-of-pg_get_sequence_data.patch

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)


--iyprHr6gT7UrymdC--