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

Commit68e9629

Browse files
Improve performance of dumpSequence().
This function dumps the sequence definitions. It is called onceper sequence, and each such call executes a query to retrieve themetadata for a single sequence. This can cause pg_dump to takesignificantly longer, especially when there are many sequences.This commit improves the performance of this function by gatheringall the sequence metadata with a single query at the beginning ofpg_dump. This information is stored in a sorted array thatdumpSequence() can bsearch() for what it needs. This follows asimilar approach as commitsd5e8930 and2329cad, whichintroduced sorted arrays for role information and pg_classinformation, respectively. As with those commits, this patch willcause pg_dump to use more memory, but that isn't expected to be tooegregious.Note that before version 10, the sequence metadata was stored inthe sequence relation itself, which makes it difficult to gatherall the sequence metadata with a single query. For those olderversions, we continue to use the preexisting query-per-sequenceapproach.Reviewed-by: Euler TaveiraDiscussion:https://postgr.es/m/20240503025140.GA1227404%40nathanxps13
1 parent23687e9 commit68e9629

File tree

2 files changed

+129
-47
lines changed

2 files changed

+129
-47
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 128 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -122,6 +122,18 @@ const char *const SeqTypeNames[] =
122122
StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
123123
"array length mismatch");
124124

125+
typedef struct
126+
{
127+
Oidoid;/* sequence OID */
128+
SeqTypeseqtype;/* data type of sequence */
129+
boolcycled;/* whether sequence cycles */
130+
int64minv;/* minimum value */
131+
int64maxv;/* maximum value */
132+
int64startv;/* start value */
133+
int64incby;/* increment value */
134+
int64cache;/* cache size */
135+
} SequenceItem;
136+
125137
typedef enum OidOptions
126138
{
127139
zeroIsError = 1,
@@ -191,6 +203,10 @@ static intnseclabels = 0;
191203
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
192204
static intnbinaryUpgradeClassOids = 0;
193205

206+
/* sorted table of sequences */
207+
static SequenceItem *sequences = NULL;
208+
static intnsequences = 0;
209+
194210
/*
195211
* The default number of rows per INSERT when
196212
* --inserts is specified without --rows-per-insert
@@ -288,6 +304,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
288304
static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
289305
static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
290306
static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
307+
static void collectSequences(Archive *fout);
291308
static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
292309
static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
293310
static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -1010,6 +1027,9 @@ main(int argc, char **argv)
10101027
if (dopt.binary_upgrade)
10111028
collectBinaryUpgradeClassOids(fout);
10121029

1030+
/* Collect sequence information. */
1031+
collectSequences(fout);
1032+
10131033
/* Lastly, create dummy objects to represent the section boundaries */
10141034
boundaryObjs = createBoundaryObjects();
10151035

@@ -17282,6 +17302,65 @@ parse_sequence_type(const char *name)
1728217302
return (SeqType) 0;/* keep compiler quiet */
1728317303
}
1728417304

17305+
/*
17306+
* bsearch() comparator for SequenceItem
17307+
*/
17308+
static int
17309+
SequenceItemCmp(const void *p1, const void *p2)
17310+
{
17311+
SequenceItem v1 = *((const SequenceItem *) p1);
17312+
SequenceItem v2 = *((const SequenceItem *) p2);
17313+
17314+
return pg_cmp_u32(v1.oid, v2.oid);
17315+
}
17316+
17317+
/*
17318+
* collectSequences
17319+
*
17320+
* Construct a table of sequence information. This table is sorted by OID for
17321+
* speed in lookup.
17322+
*/
17323+
static void
17324+
collectSequences(Archive *fout)
17325+
{
17326+
PGresult *res;
17327+
const char *query;
17328+
17329+
/*
17330+
* Before Postgres 10, sequence metadata is in the sequence itself. With
17331+
* some extra effort, we might be able to use the sorted table for those
17332+
* versions, but for now it seems unlikely to be worth it.
17333+
*/
17334+
if (fout->remoteVersion < 100000)
17335+
return;
17336+
else
17337+
query = "SELECT seqrelid, format_type(seqtypid, NULL), "
17338+
"seqstart, seqincrement, "
17339+
"seqmax, seqmin, "
17340+
"seqcache, seqcycle "
17341+
"FROM pg_catalog.pg_sequence "
17342+
"ORDER BY seqrelid";
17343+
17344+
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
17345+
17346+
nsequences = PQntuples(res);
17347+
sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
17348+
17349+
for (int i = 0; i < nsequences; i++)
17350+
{
17351+
sequences[i].oid = atooid(PQgetvalue(res, i, 0));
17352+
sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
17353+
sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
17354+
sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
17355+
sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
17356+
sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
17357+
sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
17358+
sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
17359+
}
17360+
17361+
PQclear(res);
17362+
}
17363+
1728517364
/*
1728617365
* dumpSequence
1728717366
* write the declaration (not data) of one user-defined sequence
@@ -17290,37 +17369,36 @@ static void
1729017369
dumpSequence(Archive *fout, const TableInfo *tbinfo)
1729117370
{
1729217371
DumpOptions *dopt = fout->dopt;
17293-
PGresult *res;
17294-
SeqTypeseqtype;
17295-
boolcycled;
17372+
SequenceItem *seq;
1729617373
boolis_ascending;
1729717374
int64default_minv,
17298-
default_maxv,
17299-
minv,
17300-
maxv,
17301-
startv,
17302-
incby,
17303-
cache;
17375+
default_maxv;
1730417376
PQExpBuffer query = createPQExpBuffer();
1730517377
PQExpBuffer delqry = createPQExpBuffer();
1730617378
char *qseqname;
1730717379
TableInfo *owning_tab = NULL;
1730817380

1730917381
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
1731017382

17383+
/*
17384+
* For versions >= 10, the sequence information is gathered in a sorted
17385+
* table before any calls to dumpSequence(). See collectSequences() for
17386+
* more information.
17387+
*/
1731117388
if (fout->remoteVersion >= 100000)
1731217389
{
17313-
appendPQExpBuffer(query,
17314-
"SELECT format_type(seqtypid, NULL), "
17315-
"seqstart, seqincrement, "
17316-
"seqmax, seqmin, "
17317-
"seqcache, seqcycle "
17318-
"FROM pg_catalog.pg_sequence "
17319-
"WHERE seqrelid = '%u'::oid",
17320-
tbinfo->dobj.catId.oid);
17390+
SequenceItem key = {0};
17391+
17392+
Assert(sequences);
17393+
17394+
key.oid = tbinfo->dobj.catId.oid;
17395+
seq = bsearch(&key, sequences, nsequences,
17396+
sizeof(SequenceItem), SequenceItemCmp);
1732117397
}
1732217398
else
1732317399
{
17400+
PGresult *res;
17401+
1732417402
/*
1732517403
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
1732617404
*
@@ -17332,46 +17410,47 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1733217410
"start_value, increment_by, max_value, min_value, "
1733317411
"cache_value, is_cycled FROM %s",
1733417412
fmtQualifiedDumpable(tbinfo));
17335-
}
17336-
17337-
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
1733817413

17339-
if (PQntuples(res) != 1)
17340-
pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
17341-
"query to get data of sequence \"%s\" returned %d rows (expected 1)",
17342-
PQntuples(res)),
17343-
tbinfo->dobj.name, PQntuples(res));
17414+
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
1734417415

17345-
seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
17346-
startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
17347-
incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
17348-
maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
17349-
minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
17350-
cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
17351-
cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
17416+
if (PQntuples(res) != 1)
17417+
pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
17418+
"query to get data of sequence \"%s\" returned %d rows (expected 1)",
17419+
PQntuples(res)),
17420+
tbinfo->dobj.name, PQntuples(res));
17421+
17422+
seq = pg_malloc0(sizeof(SequenceItem));
17423+
seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
17424+
seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
17425+
seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
17426+
seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
17427+
seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
17428+
seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
17429+
seq->cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
1735217430

17353-
PQclear(res);
17431+
PQclear(res);
17432+
}
1735417433

1735517434
/* Calculate default limits for a sequence of this type */
17356-
is_ascending = (incby >= 0);
17357-
if (seqtype == SEQTYPE_SMALLINT)
17435+
is_ascending = (seq->incby >= 0);
17436+
if (seq->seqtype == SEQTYPE_SMALLINT)
1735817437
{
1735917438
default_minv = is_ascending ? 1 : PG_INT16_MIN;
1736017439
default_maxv = is_ascending ? PG_INT16_MAX : -1;
1736117440
}
17362-
else if (seqtype == SEQTYPE_INTEGER)
17441+
else if (seq->seqtype == SEQTYPE_INTEGER)
1736317442
{
1736417443
default_minv = is_ascending ? 1 : PG_INT32_MIN;
1736517444
default_maxv = is_ascending ? PG_INT32_MAX : -1;
1736617445
}
17367-
else if (seqtype == SEQTYPE_BIGINT)
17446+
else if (seq->seqtype == SEQTYPE_BIGINT)
1736817447
{
1736917448
default_minv = is_ascending ? 1 : PG_INT64_MIN;
1737017449
default_maxv = is_ascending ? PG_INT64_MAX : -1;
1737117450
}
1737217451
else
1737317452
{
17374-
pg_fatal("unrecognized sequence type: %d", seqtype);
17453+
pg_fatal("unrecognized sequence type: %d",seq->seqtype);
1737517454
default_minv = default_maxv = 0;/* keep compiler quiet */
1737617455
}
1737717456

@@ -17422,27 +17501,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1742217501
"UNLOGGED " : "",
1742317502
fmtQualifiedDumpable(tbinfo));
1742417503

17425-
if (seqtype != SEQTYPE_BIGINT)
17426-
appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
17504+
if (seq->seqtype != SEQTYPE_BIGINT)
17505+
appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seq->seqtype]);
1742717506
}
1742817507

17429-
appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
17508+
appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n",seq->startv);
1743017509

17431-
appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
17510+
appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n",seq->incby);
1743217511

17433-
if (minv != default_minv)
17434-
appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
17512+
if (seq->minv != default_minv)
17513+
appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n",seq->minv);
1743517514
else
1743617515
appendPQExpBufferStr(query, " NO MINVALUE\n");
1743717516

17438-
if (maxv != default_maxv)
17439-
appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
17517+
if (seq->maxv != default_maxv)
17518+
appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n",seq->maxv);
1744017519
else
1744117520
appendPQExpBufferStr(query, " NO MAXVALUE\n");
1744217521

1744317522
appendPQExpBuffer(query,
1744417523
" CACHE " INT64_FORMAT "%s",
17445-
cache, (cycled ? "\n CYCLE" : ""));
17524+
seq->cache, (seq->cycled ? "\n CYCLE" : ""));
1744617525

1744717526
if (tbinfo->is_identity_sequence)
1744817527
{
@@ -17528,6 +17607,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1752817607
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
1752917608
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
1753017609

17610+
if (fout->remoteVersion < 100000)
17611+
pg_free(seq);
1753117612
destroyPQExpBuffer(query);
1753217613
destroyPQExpBuffer(delqry);
1753317614
free(qseqname);

‎src/tools/pgindent/typedefs.list

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2573,6 +2573,7 @@ SeqScanState
25732573
SeqTable
25742574
SeqTableData
25752575
SeqType
2576+
SequenceItem
25762577
SerCommitSeqNo
25772578
SerialControl
25782579
SerialIOData

0 commit comments

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