From b73a2f979c0f5104c085cb8d52f9c0b441994e32 Mon Sep 17 00:00:00 2001 From: Andrew Krylosov Date: Sat, 26 Sep 2026 21:57:45 +0300 Subject: [PATCH v1] pg_dump: Fetch sequence data only for sequences being dumped MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Since commit bd15b7db48, collectSequences() reads the data of every sequence in the database: its query calls pg_get_sequence_data() for each row of pg_sequence, even when only a few sequences are going to be dumped, e.g. with --schema or --table. That function opens, locks, and reads each sequence, so in databases with many sequences a selective dump became much slower than it was before v18. Such a dump could also block on a lock held on a sequence it doesn't dump, for example one being dropped by an uncommitted transaction. To fix, pass the OIDs of the sequences whose data will be dumped to the query and call pg_get_sequence_data() only for those. Bug: #19688 Reported-by: César García Naranjo Author: Andrew Krylosov Discussion: https://postgr.es/m/19688-e90025dc375a22a3@postgresql.org Discussion: https://postgr.es/m/1862355.1767827628@sss.pgh.pa.us Backpatch-through: 18 --- src/bin/pg_dump/pg_dump.c | 83 ++++++++++++++++++++++++++++----------- 1 file changed, 61 insertions(+), 22 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 618b0ea28a..9ad6d1f422 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -311,7 +311,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 collectSequences(Archive *fout, TableInfo *tblinfo, int numTables); static void dumpSequence(Archive *fout, const TableInfo *tbinfo); static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo); static void dumpIndex(Archive *fout, const IndxInfo *indxinfo); @@ -1141,7 +1141,7 @@ main(int argc, char **argv) collectBinaryUpgradeClassOids(fout); /* Collect sequence information. */ - collectSequences(fout); + collectSequences(fout, tblinfo, numTables); /* Lastly, create dummy objects to represent the section boundaries */ boundaryObjs = createBoundaryObjects(); @@ -18728,10 +18728,10 @@ SequenceItemCmp(const void *p1, const void *p2) * speed in lookup. */ static void -collectSequences(Archive *fout) +collectSequences(Archive *fout, TableInfo *tblinfo, int numTables) { + PQExpBuffer query; PGresult *res; - const char *query; /* * Before Postgres 10, sequence metadata is in the sequence itself. With @@ -18743,26 +18743,64 @@ collectSequences(Archive *fout) */ if (fout->remoteVersion < 100000) return; - else if (fout->remoteVersion < 180000 || - (!fout->dopt->dumpData && !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"; + + query = createPQExpBuffer(); + + if (fout->remoteVersion < 180000 || + (!fout->dopt->dumpData && !fout->dopt->sequence_data)) + appendPQExpBufferStr(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_get_sequence_data(seqrelid) " - "ORDER BY seqrelid;"; + { + PQExpBuffer seqoids = createPQExpBuffer(); - res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK); + /* + * pg_get_sequence_data() has to open, lock, and read each sequence, + * so call it only for the sequences whose data will be dumped, i.e., + * those that already have a TableDataInfo. Otherwise, dumping a few + * sequences from a database that has many would be slow, and it could + * block on locks held on sequences we're not dumping. We still + * collect the definitions of all sequences, which is cheap. + */ + appendPQExpBufferChar(seqoids, '{'); + for (int i = 0; i < numTables; i++) + { + TableInfo *tbinfo = &tblinfo[i]; + + if (tbinfo->relkind != RELKIND_SEQUENCE || tbinfo->dataObj == NULL) + continue; + + if (seqoids->len > 1) /* do we have more than the '{'? */ + appendPQExpBufferChar(seqoids, ','); + appendPQExpBuffer(seqoids, "%u", tbinfo->dobj.catId.oid); + } + appendPQExpBufferChar(seqoids, '}'); + + appendPQExpBuffer(query, + "SELECT s.seqrelid, format_type(s.seqtypid, NULL), " + "s.seqstart, s.seqincrement, " + "s.seqmax, s.seqmin, " + "s.seqcache, s.seqcycle, " + "d.last_value, d.is_called " + "FROM pg_catalog.pg_sequence s " + "LEFT JOIN (SELECT src.seqrelid, " + "sd.last_value, sd.is_called " + "FROM unnest('%s'::pg_catalog.oid[]) AS src(seqrelid), " + "pg_get_sequence_data(src.seqrelid) AS sd) d " + "ON d.seqrelid = s.seqrelid " + "ORDER BY s.seqrelid", + seqoids->data); + + destroyPQExpBuffer(seqoids); + } + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); nsequences = PQntuples(res); sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem)); @@ -18783,6 +18821,7 @@ collectSequences(Archive *fout) } PQclear(res); + destroyPQExpBuffer(query); } /* base-commit: 2d748cfe337ec5afab3863f733caca43333c89cf -- 2.50.1 (Apple Git-155)