public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
To: Tom Lane <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Euler Taveira <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: improve performance of pg_dump with many sequences
Date: Thu, 18 Jul 2024 15:29:19 -0500
Message-ID: <Zpl7HywbF2qAkTVf@nathan> (raw)
In-Reply-To: <ZpldUoP0XjHszExF@nathan>
References: <Zpbnz4P13W7WICj-@nathan>
<[email protected]>
<Zpc5HGb7yDfTNDy5@nathan>
<ZpgR4OMpbekeja_n@nathan>
<[email protected]>
<ZpgV32NTM0ADvJop@nathan>
<[email protected]>
<ZpiP3I6UVcgsCCWf@nathan>
<[email protected]>
<ZpldUoP0XjHszExF@nathan>
I fixed a compiler warning on Windows in v6 of the patch set. Sorry for
the noise.
--
nathan
From 139826d3b9290d547ae9b3446ad455ba713211e0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v6 1/4] parse sequence information
---
src/bin/pg_dump/pg_dump.c | 103 ++++++++++++++++++-------------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 60 insertions(+), 44 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..74e22e5e1e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,23 @@ typedef struct
RelFileNumber toast_index_relfilenumber; /* toast table index filenode */
} BinaryUpgradeClassOidItem;
+typedef enum SeqType
+{
+ SEQTYPE_SMALLINT,
+ SEQTYPE_INTEGER,
+ SEQTYPE_BIGINT,
+} SeqType;
+
+const char *const SeqTypeNames[] =
+{
+ [SEQTYPE_SMALLINT] = "smallint",
+ [SEQTYPE_INTEGER] = "integer",
+ [SEQTYPE_BIGINT] = "bigint",
+};
+
+StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
+ "array length mismatch");
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -17189,6 +17206,19 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
free(qtabname);
}
+static inline SeqType
+parse_sequence_type(const char *name)
+{
+ for (int i = 0; i < lengthof(SeqTypeNames); i++)
+ {
+ if (strcmp(SeqTypeNames[i], name) == 0)
+ return (SeqType) i;
+ }
+
+ pg_fatal("unrecognized sequence type: %s", name);
+ return (SeqType) 0; /* keep compiler quiet */
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17198,18 +17228,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
PGresult *res;
- char *startv,
- *incby,
- *maxv,
- *minv,
- *cache,
- *seqtype;
+ SeqType seqtype;
bool cycled;
bool is_ascending;
int64 default_minv,
- default_maxv;
- char bufm[32],
- bufx[32];
+ default_maxv,
+ minv,
+ maxv,
+ startv,
+ incby,
+ cache;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17251,50 +17279,39 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
PQntuples(res)),
tbinfo->dobj.name, PQntuples(res));
- seqtype = PQgetvalue(res, 0, 0);
- startv = PQgetvalue(res, 0, 1);
- incby = PQgetvalue(res, 0, 2);
- maxv = PQgetvalue(res, 0, 3);
- minv = PQgetvalue(res, 0, 4);
- cache = PQgetvalue(res, 0, 5);
+ seqtype = parse_sequence_type(PQgetvalue(res, 0, 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);
+
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby[0] != '-');
- if (strcmp(seqtype, "smallint") == 0)
+ is_ascending = (incby >= 0);
+ if (seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (strcmp(seqtype, "integer") == 0)
+ else if (seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (strcmp(seqtype, "bigint") == 0)
+ else if (seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %s", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
- /*
- * 64-bit strtol() isn't very portable, so convert the limits to strings
- * and compare that way.
- */
- snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
- snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
- /* Don't print minv/maxv if they match the respective default limit */
- if (strcmp(minv, bufm) == 0)
- minv = NULL;
- if (strcmp(maxv, bufx) == 0)
- maxv = NULL;
-
/*
* Identity sequences are not to be dropped separately.
*/
@@ -17342,26 +17359,26 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (strcmp(seqtype, "bigint") != 0)
- appendPQExpBuffer(query, " AS %s\n", seqtype);
+ if (seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
}
- appendPQExpBuffer(query, " START WITH %s\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
- appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
- if (minv)
- appendPQExpBuffer(query, " MINVALUE %s\n", minv);
+ if (minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv)
- appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
+ if (maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
- " CACHE %s%s",
+ " CACHE " INT64_FORMAT "%s",
cache, (cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
@@ -17448,8 +17465,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
- PQclear(res);
-
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..974e21155a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
SeqScanState
SeqTable
SeqTableData
+SeqType
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
From 7bf81a53af63c993478bb47fe347deefd1145bb4 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v6 2/4] cache sequence information
---
src/bin/pg_dump/pg_dump.c | 175 ++++++++++++++++++++++---------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 129 insertions(+), 47 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 74e22e5e1e..8c42fd762d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -121,6 +121,18 @@ const char *const SeqTypeNames[] =
StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
"array length mismatch");
+typedef struct
+{
+ Oid oid; /* sequence OID */
+ SeqType seqtype; /* data type of sequence */
+ bool cycled; /* whether sequence cycles */
+ int64 minv; /* minimum value */
+ int64 maxv; /* maximum value */
+ int64 startv; /* start value */
+ int64 incby; /* increment value */
+ int64 cache; /* cache size */
+} SequenceItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -190,6 +202,10 @@ static int nseclabels = 0;
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
static int nbinaryUpgradeClassOids = 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
@@ -287,6 +303,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);
@@ -1009,6 +1026,9 @@ main(int argc, char **argv)
if (dopt.binary_upgrade)
collectBinaryUpgradeClassOids(fout);
+ /* Collect sequence information. */
+ collectSequences(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -17219,6 +17239,65 @@ parse_sequence_type(const char *name)
return (SeqType) 0; /* keep compiler quiet */
}
+/*
+ * 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;
+
+ /*
+ * 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.
+ */
+ if (fout->remoteVersion < 100000)
+ return;
+ else
+ 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++)
+ {
+ sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+ sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
+ 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
@@ -17227,17 +17306,10 @@ static void
dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
- PGresult *res;
- SeqType seqtype;
- bool cycled;
+ SequenceItem *seq;
bool is_ascending;
int64 default_minv,
- default_maxv,
- minv,
- maxv,
- startv,
- incby,
- cache;
+ default_maxv;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17245,19 +17317,25 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
+ /*
+ * For versions >= 10, the sequence information is gathered in a sorted
+ * table before any calls to dumpSequence(). See collectSequences() for
+ * more information.
+ */
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};
+
+ Assert(sequences);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ seq = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
}
else
{
+ PGresult *res;
+
/*
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
*
@@ -17269,46 +17347,47 @@ 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));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- seqtype = parse_sequence_type(PQgetvalue(res, 0, 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);
+ 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));
+
+ seq = pg_malloc0(sizeof(SequenceItem));
+ seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+ seq->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);
- if (seqtype == SEQTYPE_SMALLINT)
+ is_ascending = (seq->incby >= 0);
+ if (seq->seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (seqtype == SEQTYPE_INTEGER)
+ else if (seq->seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (seqtype == SEQTYPE_BIGINT)
+ else if (seq->seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %d", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seq->seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
@@ -17359,27 +17438,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (seqtype != SEQTYPE_BIGINT)
- appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
+ if (seq->seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seq->seqtype]);
}
- appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", seq->startv);
- appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", seq->incby);
- if (minv != default_minv)
- appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
+ if (seq->minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", seq->minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv != default_maxv)
- appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
+ if (seq->maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", seq->maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
" CACHE " INT64_FORMAT "%s",
- cache, (cycled ? "\n CYCLE" : ""));
+ seq->cache, (seq->cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
{
@@ -17465,6 +17544,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
+ if (fout->remoteVersion < 100000)
+ pg_free(seq);
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 974e21155a..e1a46b5bf5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2573,6 +2573,7 @@ SeqScanState
SeqTable
SeqTableData
SeqType
+SequenceItem
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
From e83ca9e2312e31ccf4430e8fd24744eff3d6fe75 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v6 3/4] introduce pg_sequence_read_tuple
XXX: NEEDS CATVERSION BUMP
---
src/backend/commands/sequence.c | 62 ++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +++
src/test/regress/expected/sequence.out | 7 +++
src/test/regress/sql/sequence.sql | 3 ++
4 files changed, 78 insertions(+)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..364097c457 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
}
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ SeqTable elm;
+ Relation seqrel;
+ Datum values[SEQ_COL_LASTCOL];
+ bool isnull[SEQ_COL_LASTCOL];
+ TupleDesc resultTupleDesc;
+ HeapTuple resultHeapTuple;
+ Datum result;
+
+ resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+ BOOLOID, -1, 0);
+ resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+ init_sequence(relid, &elm, &seqrel);
+
+ /*
+ * Return all NULLs for 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 &&
+ !RELATION_IS_OTHER_TEMP(seqrel) &&
+ (RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+ {
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence_data seq;
+
+ seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum(seq->last_value);
+ values[1] = Int64GetDatum(seq->log_cnt);
+ values[2] = BoolGetDatum(seq->is_called);
+
+ UnlockReleaseBuffer(buf);
+ }
+ else
+ memset(isnull, true, sizeof(isnull));
+
+ sequence_close(seqrel, NoLock);
+
+ resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+ result = HeapTupleGetDatum(resultHeapTuple);
+ PG_RETURN_DATUM(result);
+}
+
+
/*
* Return the last value from the sequence
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass',
prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+ proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+ prorettype => 'record', proargtypes => 'regclass',
+ proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+ proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+ prosrc => 'pg_sequence_read_tuple' },
{ oid => '275', descr => 'return the next oid for a system table',
proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
3
(1 row)
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 10 | 32 | t
+(1 row)
+
DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
DROP SEQUENCE test_seq1;
--
2.39.3 (Apple Git-146)
From c339097c8a6be238798d14ec08d157d818bdf3fc Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v6 4/4] cache sequence data
---
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)
Attachments:
[text/plain] v6-0001-parse-sequence-information.patch (6.0K, ../Zpl7HywbF2qAkTVf@nathan/2-v6-0001-parse-sequence-information.patch)
download | inline diff:
From 139826d3b9290d547ae9b3446ad455ba713211e0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v6 1/4] parse sequence information
---
src/bin/pg_dump/pg_dump.c | 103 ++++++++++++++++++-------------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 60 insertions(+), 44 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..74e22e5e1e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,23 @@ typedef struct
RelFileNumber toast_index_relfilenumber; /* toast table index filenode */
} BinaryUpgradeClassOidItem;
+typedef enum SeqType
+{
+ SEQTYPE_SMALLINT,
+ SEQTYPE_INTEGER,
+ SEQTYPE_BIGINT,
+} SeqType;
+
+const char *const SeqTypeNames[] =
+{
+ [SEQTYPE_SMALLINT] = "smallint",
+ [SEQTYPE_INTEGER] = "integer",
+ [SEQTYPE_BIGINT] = "bigint",
+};
+
+StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
+ "array length mismatch");
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -17189,6 +17206,19 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
free(qtabname);
}
+static inline SeqType
+parse_sequence_type(const char *name)
+{
+ for (int i = 0; i < lengthof(SeqTypeNames); i++)
+ {
+ if (strcmp(SeqTypeNames[i], name) == 0)
+ return (SeqType) i;
+ }
+
+ pg_fatal("unrecognized sequence type: %s", name);
+ return (SeqType) 0; /* keep compiler quiet */
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17198,18 +17228,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
PGresult *res;
- char *startv,
- *incby,
- *maxv,
- *minv,
- *cache,
- *seqtype;
+ SeqType seqtype;
bool cycled;
bool is_ascending;
int64 default_minv,
- default_maxv;
- char bufm[32],
- bufx[32];
+ default_maxv,
+ minv,
+ maxv,
+ startv,
+ incby,
+ cache;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17251,50 +17279,39 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
PQntuples(res)),
tbinfo->dobj.name, PQntuples(res));
- seqtype = PQgetvalue(res, 0, 0);
- startv = PQgetvalue(res, 0, 1);
- incby = PQgetvalue(res, 0, 2);
- maxv = PQgetvalue(res, 0, 3);
- minv = PQgetvalue(res, 0, 4);
- cache = PQgetvalue(res, 0, 5);
+ seqtype = parse_sequence_type(PQgetvalue(res, 0, 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);
+
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby[0] != '-');
- if (strcmp(seqtype, "smallint") == 0)
+ is_ascending = (incby >= 0);
+ if (seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (strcmp(seqtype, "integer") == 0)
+ else if (seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (strcmp(seqtype, "bigint") == 0)
+ else if (seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %s", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
- /*
- * 64-bit strtol() isn't very portable, so convert the limits to strings
- * and compare that way.
- */
- snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
- snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
- /* Don't print minv/maxv if they match the respective default limit */
- if (strcmp(minv, bufm) == 0)
- minv = NULL;
- if (strcmp(maxv, bufx) == 0)
- maxv = NULL;
-
/*
* Identity sequences are not to be dropped separately.
*/
@@ -17342,26 +17359,26 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (strcmp(seqtype, "bigint") != 0)
- appendPQExpBuffer(query, " AS %s\n", seqtype);
+ if (seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
}
- appendPQExpBuffer(query, " START WITH %s\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
- appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
- if (minv)
- appendPQExpBuffer(query, " MINVALUE %s\n", minv);
+ if (minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv)
- appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
+ if (maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
- " CACHE %s%s",
+ " CACHE " INT64_FORMAT "%s",
cache, (cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
@@ -17448,8 +17465,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
- PQclear(res);
-
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..974e21155a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
SeqScanState
SeqTable
SeqTableData
+SeqType
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
[text/plain] v6-0002-cache-sequence-information.patch (10.2K, ../Zpl7HywbF2qAkTVf@nathan/3-v6-0002-cache-sequence-information.patch)
download | inline diff:
From 7bf81a53af63c993478bb47fe347deefd1145bb4 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v6 2/4] cache sequence information
---
src/bin/pg_dump/pg_dump.c | 175 ++++++++++++++++++++++---------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 129 insertions(+), 47 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 74e22e5e1e..8c42fd762d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -121,6 +121,18 @@ const char *const SeqTypeNames[] =
StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
"array length mismatch");
+typedef struct
+{
+ Oid oid; /* sequence OID */
+ SeqType seqtype; /* data type of sequence */
+ bool cycled; /* whether sequence cycles */
+ int64 minv; /* minimum value */
+ int64 maxv; /* maximum value */
+ int64 startv; /* start value */
+ int64 incby; /* increment value */
+ int64 cache; /* cache size */
+} SequenceItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -190,6 +202,10 @@ static int nseclabels = 0;
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
static int nbinaryUpgradeClassOids = 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
@@ -287,6 +303,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);
@@ -1009,6 +1026,9 @@ main(int argc, char **argv)
if (dopt.binary_upgrade)
collectBinaryUpgradeClassOids(fout);
+ /* Collect sequence information. */
+ collectSequences(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -17219,6 +17239,65 @@ parse_sequence_type(const char *name)
return (SeqType) 0; /* keep compiler quiet */
}
+/*
+ * 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;
+
+ /*
+ * 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.
+ */
+ if (fout->remoteVersion < 100000)
+ return;
+ else
+ 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++)
+ {
+ sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+ sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
+ 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
@@ -17227,17 +17306,10 @@ static void
dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
- PGresult *res;
- SeqType seqtype;
- bool cycled;
+ SequenceItem *seq;
bool is_ascending;
int64 default_minv,
- default_maxv,
- minv,
- maxv,
- startv,
- incby,
- cache;
+ default_maxv;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17245,19 +17317,25 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
+ /*
+ * For versions >= 10, the sequence information is gathered in a sorted
+ * table before any calls to dumpSequence(). See collectSequences() for
+ * more information.
+ */
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};
+
+ Assert(sequences);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ seq = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
}
else
{
+ PGresult *res;
+
/*
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
*
@@ -17269,46 +17347,47 @@ 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));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- seqtype = parse_sequence_type(PQgetvalue(res, 0, 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);
+ 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));
+
+ seq = pg_malloc0(sizeof(SequenceItem));
+ seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+ seq->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);
- if (seqtype == SEQTYPE_SMALLINT)
+ is_ascending = (seq->incby >= 0);
+ if (seq->seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (seqtype == SEQTYPE_INTEGER)
+ else if (seq->seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (seqtype == SEQTYPE_BIGINT)
+ else if (seq->seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %d", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seq->seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
@@ -17359,27 +17438,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (seqtype != SEQTYPE_BIGINT)
- appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
+ if (seq->seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seq->seqtype]);
}
- appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", seq->startv);
- appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", seq->incby);
- if (minv != default_minv)
- appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
+ if (seq->minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", seq->minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv != default_maxv)
- appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
+ if (seq->maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", seq->maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
" CACHE " INT64_FORMAT "%s",
- cache, (cycled ? "\n CYCLE" : ""));
+ seq->cache, (seq->cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
{
@@ -17465,6 +17544,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
+ if (fout->remoteVersion < 100000)
+ pg_free(seq);
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 974e21155a..e1a46b5bf5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2573,6 +2573,7 @@ SeqScanState
SeqTable
SeqTableData
SeqType
+SequenceItem
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
[text/plain] v6-0003-introduce-pg_sequence_read_tuple.patch (4.4K, ../Zpl7HywbF2qAkTVf@nathan/4-v6-0003-introduce-pg_sequence_read_tuple.patch)
download | inline diff:
From e83ca9e2312e31ccf4430e8fd24744eff3d6fe75 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v6 3/4] introduce pg_sequence_read_tuple
XXX: NEEDS CATVERSION BUMP
---
src/backend/commands/sequence.c | 62 ++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +++
src/test/regress/expected/sequence.out | 7 +++
src/test/regress/sql/sequence.sql | 3 ++
4 files changed, 78 insertions(+)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..364097c457 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
}
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ SeqTable elm;
+ Relation seqrel;
+ Datum values[SEQ_COL_LASTCOL];
+ bool isnull[SEQ_COL_LASTCOL];
+ TupleDesc resultTupleDesc;
+ HeapTuple resultHeapTuple;
+ Datum result;
+
+ resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+ BOOLOID, -1, 0);
+ resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+ init_sequence(relid, &elm, &seqrel);
+
+ /*
+ * Return all NULLs for 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 &&
+ !RELATION_IS_OTHER_TEMP(seqrel) &&
+ (RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+ {
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence_data seq;
+
+ seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum(seq->last_value);
+ values[1] = Int64GetDatum(seq->log_cnt);
+ values[2] = BoolGetDatum(seq->is_called);
+
+ UnlockReleaseBuffer(buf);
+ }
+ else
+ memset(isnull, true, sizeof(isnull));
+
+ sequence_close(seqrel, NoLock);
+
+ resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+ result = HeapTupleGetDatum(resultHeapTuple);
+ PG_RETURN_DATUM(result);
+}
+
+
/*
* Return the last value from the sequence
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass',
prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+ proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+ prorettype => 'record', proargtypes => 'regclass',
+ proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+ proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+ prosrc => 'pg_sequence_read_tuple' },
{ oid => '275', descr => 'return the next oid for a system table',
proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
3
(1 row)
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 10 | 32 | t
+(1 row)
+
DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
DROP SEQUENCE test_seq1;
--
2.39.3 (Apple Git-146)
[text/plain] v6-0004-cache-sequence-data.patch (4.9K, ../Zpl7HywbF2qAkTVf@nathan/5-v6-0004-cache-sequence-data.patch)
download | inline diff:
From c339097c8a6be238798d14ec08d157d818bdf3fc Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v6 4/4] cache sequence data
---
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)
view thread (14+ 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], [email protected], [email protected], [email protected]
Subject: Re: improve performance of pg_dump with many sequences
In-Reply-To: <Zpl7HywbF2qAkTVf@nathan>
* 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