agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
Subject: [PATCH v6 4/4] cache sequence data
Date: Wed, 17 Jul 2024 22:13:08 -0500
---
src/bin/pg_dump/pg_dump.c | 81 ++++++++++++++++++++++++++++++---------
1 file changed, 63 insertions(+), 18 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8c42fd762d..78f6f608bc 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -131,6 +131,8 @@ typedef struct
int64 startv; /* start value */
int64 incby; /* increment value */
int64 cache; /* cache size */
+ int64 last_value; /* last value of sequence */
+ bool is_called; /* whether nextval advances before returning */
} SequenceItem;
typedef enum OidOptions
@@ -17267,16 +17269,30 @@ collectSequences(Archive *fout)
* Before Postgres 10, sequence metadata is in the sequence itself. We
* could likely make use of the sorted table with some extra effort, but
* for now it seems unlikely to be worth it.
+ *
+ * Since version 18, we can gather the sequence data in this query with
+ * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
*/
if (fout->remoteVersion < 100000)
return;
- else
+ else if (fout->remoteVersion < 180000 ||
+ (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
query = "SELECT seqrelid, format_type(seqtypid, NULL), "
"seqstart, seqincrement, "
"seqmax, seqmin, "
- "seqcache, seqcycle "
+ "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_sequence_read_tuple(seqrelid) "
+ "ORDER BY seqrelid;";
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
@@ -17293,6 +17309,8 @@ collectSequences(Archive *fout)
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);
+ sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+ sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
}
PQclear(res);
@@ -17559,30 +17577,59 @@ static void
dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
{
TableInfo *tbinfo = tdinfo->tdtable;
- PGresult *res;
- char *last;
+ int64 last;
bool called;
PQExpBuffer query = createPQExpBuffer();
- appendPQExpBuffer(query,
- "SELECT last_value, is_called FROM %s",
- fmtQualifiedDumpable(tbinfo));
+ /*
+ * For versions >= 18, the sequence information is gathered in the sorted
+ * array before any calls to dumpSequenceData(). See collectSequences()
+ * for more information.
+ *
+ * For older versions, we have to query the sequence relations
+ * individually.
+ */
+ if (fout->remoteVersion < 180000)
+ {
+ PGresult *res;
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ appendPQExpBuffer(query,
+ "SELECT last_value, is_called FROM %s",
+ fmtQualifiedDumpable(tbinfo));
- 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));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- last = PQgetvalue(res, 0, 0);
- called = (strcmp(PQgetvalue(res, 0, 1), "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));
+
+ last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+ called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+ PQclear(res);
+ }
+ else
+ {
+ SequenceItem key = {0};
+ SequenceItem *entry;
+
+ Assert(sequences);
+ Assert(tbinfo->dobj.catId.oid);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ entry = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
+
+ last = entry->last_value;
+ called = entry->is_called;
+ }
resetPQExpBuffer(query);
appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
- appendPQExpBuffer(query, ", %s, %s);\n",
+ appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
last, (called ? "true" : "false"));
if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17596,8 +17643,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
.deps = &(tbinfo->dobj.dumpId),
.nDeps = 1));
- PQclear(res);
-
destroyPQExpBuffer(query);
}
--
2.39.3 (Apple Git-146)
--zrchpDNjnCAXEpsr--
view thread (4+ messages)
reply
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: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v6 4/4] cache sequence data
In-Reply-To: <no-message-id-64579@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox