Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbd15b7d

Browse files
Improve performance of dumpSequenceData().
As one might guess, this function dumps the sequence data. It iscalled once per sequence, and each such call executes a query toretrieve the relevant data for a single sequence. This can causepg_dump to take significantly longer, especially when there aremany sequences.This commit improves the performance of this function by gatheringall the sequence data with a single query at the beginning ofpg_dump. This information is stored in a sorted array thatdumpSequenceData() can bsearch() for what it needs. This follows asimilar approach as previous commits that introduced sorted arraysfor role information, pg_class information, and sequence metadata.As with those commits, this patch will cause pg_dump to use morememory, but that isn't expected to be too egregious.Note that we use the brand new function pg_sequence_read_tuple() inthe query that gathers all sequence data, so we must continue touse the preexisting query-per-sequence approach for versions olderthan 18.Reviewed-by: Euler Taveira, Michael Paquier, Tom LaneDiscussion:https://postgr.es/m/20240503025140.GA1227404%40nathanxps13
1 parentc8b06bb commitbd15b7d

File tree

1 file changed

+63
-18
lines changed

1 file changed

+63
-18
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 63 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,8 @@ typedef struct
132132
int64startv;/* start value */
133133
int64incby;/* increment value */
134134
int64cache;/* cache size */
135+
int64last_value;/* last value of sequence */
136+
boolis_called;/* whether nextval advances before returning */
135137
} SequenceItem;
136138

137139
typedef enum OidOptions
@@ -17330,16 +17332,30 @@ collectSequences(Archive *fout)
1733017332
* Before Postgres 10, sequence metadata is in the sequence itself. With
1733117333
* some extra effort, we might be able to use the sorted table for those
1733217334
* versions, but for now it seems unlikely to be worth it.
17335+
*
17336+
* Since version 18, we can gather the sequence data in this query with
17337+
* pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
1733317338
*/
1733417339
if (fout->remoteVersion < 100000)
1733517340
return;
17336-
else
17341+
else if (fout->remoteVersion < 180000 ||
17342+
(fout->dopt->schemaOnly && !fout->dopt->sequence_data))
1733717343
query = "SELECT seqrelid, format_type(seqtypid, NULL), "
1733817344
"seqstart, seqincrement, "
1733917345
"seqmax, seqmin, "
17340-
"seqcache, seqcycle "
17346+
"seqcache, seqcycle, "
17347+
"NULL, 'f' "
1734117348
"FROM pg_catalog.pg_sequence "
1734217349
"ORDER BY seqrelid";
17350+
else
17351+
query = "SELECT seqrelid, format_type(seqtypid, NULL), "
17352+
"seqstart, seqincrement, "
17353+
"seqmax, seqmin, "
17354+
"seqcache, seqcycle, "
17355+
"last_value, is_called "
17356+
"FROM pg_catalog.pg_sequence, "
17357+
"pg_sequence_read_tuple(seqrelid) "
17358+
"ORDER BY seqrelid;";
1734317359

1734417360
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
1734517361

@@ -17356,6 +17372,8 @@ collectSequences(Archive *fout)
1735617372
sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
1735717373
sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
1735817374
sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
17375+
sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
17376+
sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
1735917377
}
1736017378

1736117379
PQclear(res);
@@ -17622,30 +17640,59 @@ static void
1762217640
dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
1762317641
{
1762417642
TableInfo *tbinfo = tdinfo->tdtable;
17625-
PGresult *res;
17626-
char *last;
17643+
int64last;
1762717644
boolcalled;
1762817645
PQExpBuffer query = createPQExpBuffer();
1762917646

17630-
appendPQExpBuffer(query,
17631-
"SELECT last_value, is_called FROM %s",
17632-
fmtQualifiedDumpable(tbinfo));
17647+
/*
17648+
* For versions >= 18, the sequence information is gathered in the sorted
17649+
* array before any calls to dumpSequenceData(). See collectSequences()
17650+
* for more information.
17651+
*
17652+
* For older versions, we have to query the sequence relations
17653+
* individually.
17654+
*/
17655+
if (fout->remoteVersion < 180000)
17656+
{
17657+
PGresult *res;
1763317658

17634-
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
17659+
appendPQExpBuffer(query,
17660+
"SELECT last_value, is_called FROM %s",
17661+
fmtQualifiedDumpable(tbinfo));
1763517662

17636-
if (PQntuples(res) != 1)
17637-
pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
17638-
"query to get data of sequence \"%s\" returned %d rows (expected 1)",
17639-
PQntuples(res)),
17640-
tbinfo->dobj.name, PQntuples(res));
17663+
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
1764117664

17642-
last = PQgetvalue(res, 0, 0);
17643-
called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
17665+
if (PQntuples(res) != 1)
17666+
pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
17667+
"query to get data of sequence \"%s\" returned %d rows (expected 1)",
17668+
PQntuples(res)),
17669+
tbinfo->dobj.name, PQntuples(res));
17670+
17671+
last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
17672+
called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
17673+
17674+
PQclear(res);
17675+
}
17676+
else
17677+
{
17678+
SequenceItem key = {0};
17679+
SequenceItem *entry;
17680+
17681+
Assert(sequences);
17682+
Assert(tbinfo->dobj.catId.oid);
17683+
17684+
key.oid = tbinfo->dobj.catId.oid;
17685+
entry = bsearch(&key, sequences, nsequences,
17686+
sizeof(SequenceItem), SequenceItemCmp);
17687+
17688+
last = entry->last_value;
17689+
called = entry->is_called;
17690+
}
1764417691

1764517692
resetPQExpBuffer(query);
1764617693
appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
1764717694
appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
17648-
appendPQExpBuffer(query, ",%s, %s);\n",
17695+
appendPQExpBuffer(query, "," INT64_FORMAT ", %s);\n",
1764917696
last, (called ? "true" : "false"));
1765017697

1765117698
if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17659,8 +17706,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
1765917706
.deps = &(tbinfo->dobj.dumpId),
1766017707
.nDeps = 1));
1766117708

17662-
PQclear(res);
17663-
1766417709
destroyPQExpBuffer(query);
1766517710
}
1766617711

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp