From a58f6c33029659aef0a2139293da5ba9fd7f178f Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Tue, 30 Apr 2024 15:18:52 -0500 Subject: [PATCH v1 2/3] cache sequence information --- src/bin/pg_dump/pg_dump.c | 143 +++++++++++++++++++++++++------ src/tools/pgindent/typedefs.list | 1 + 2 files changed, 116 insertions(+), 28 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index b53c17aace..98cc2698ac 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -55,6 +55,7 @@ #include "catalog/pg_trigger_d.h" #include "catalog/pg_type_d.h" #include "common/connect.h" +#include "common/int.h" #include "common/relpath.h" #include "compress_io.h" #include "dumputils.h" @@ -92,6 +93,18 @@ typedef struct int objsubid; /* subobject (table column #) */ } SecLabelItem; +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, @@ -157,6 +170,10 @@ static int ncomments = 0; static SecLabelItem *seclabels = NULL; static int nseclabels = 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 @@ -254,6 +271,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); @@ -971,6 +989,9 @@ main(int argc, char **argv) if (!dopt.no_security_labels) collectSecLabels(fout); + /* Collect sequence information. */ + collectSequences(fout); + /* Lastly, create dummy objects to represent the section boundaries */ boundaryObjs = createBoundaryObjects(); @@ -17557,6 +17578,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 @@ -17565,7 +17643,6 @@ static void dumpSequence(Archive *fout, const TableInfo *tbinfo) { DumpOptions *dopt = fout->dopt; - PGresult *res; char seqtype[10]; bool cycled; bool is_ascending; @@ -17585,17 +17662,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. * @@ -17607,28 +17694,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 e10ff28ee5..ff7a4ca7e1 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2555,6 +2555,7 @@ SeqScan SeqScanState SeqTable SeqTableData +SequenceItem SerCommitSeqNo SerialControl SerialIOData -- 2.25.1