On Wed, Jan 07, 2026 at 07:24:52PM -0500, Tom Lane wrote: > Nathan Bossart <[email protected]> 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
>From aeabbd9b6e336d086941eb89f2b4983b58c5881e Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> 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)
