From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Euler Taveira <euler@eulerto.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: improve performance of pg_dump with many sequences
Date: Thu, 8 Jan 2026 11:36:38 -0600
Message-ID: <aV_rJv-JnMh6WWQw@nathan> (raw)
In-Reply-To: <1869482.1767831892@sss.pgh.pa.us>
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>
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
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.cindex 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.cindex 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.cindex 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.hindex 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)
Attachments:
[text/plain] v1-0001-pg_dump-fix-use-of-pg_get_sequence_data.patch (5.0K, ../aV_rJv-JnMh6WWQw@nathan/2-v1-0001-pg_dump-fix-use-of-pg_get_sequence_data.patch)
download | inline diff:
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.cindex 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.cindex 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.cindex 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.hindex 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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, tgl@sss.pgh.pa.us, michael@paquier.xyz, euler@eulerto.com
Subject: Re: improve performance of pg_dump with many sequences
In-Reply-To: <aV_rJv-JnMh6WWQw@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox