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
Appearance settings

Commit 2329cad

Browse filesBrowse files
nathan-bossartnathan-bossart
committed
Improve performance of binary_upgrade_set_pg_class_oids().
This function generates the commands that preserve the OIDs and relfilenodes of relations during pg_upgrade. It is called once per relevant relation, and each such call executes a relatively expensive query to retrieve information for a single pg_class_oid. This can cause pg_dump to take significantly longer when --binary-upgrade is specified, especially when there are many tables. This commit improves the performance of this function by gathering all the required pg_class information with a single query at the beginning of pg_dump. This information is stored in a sorted array that binary_upgrade_set_pg_class_oids() can bsearch() for what it needs. This follows a similar approach as commit d5e8930, which introduced a sorted array for role information. With this patch, 'pg_dump --binary-upgrade' will use more memory, but that isn't expected to be too egregious. Per the mailing list discussion, folks feel that this is worth the trade-off. Reviewed-by: Corey Huinker, Michael Paquier, Daniel Gustafsson Discussion: https://postgr.es/m/20240418041712.GA3441570%40nathanxps13
1 parent 6e1c4a0 commit 2329cad
Copy full SHA for 2329cad

File tree

Expand file treeCollapse file tree

2 files changed

+95
-46
lines changed
Filter options
Expand file treeCollapse file tree

2 files changed

+95
-46
lines changed

‎src/bin/pg_dump/pg_dump.c

Copy file name to clipboardExpand all lines: src/bin/pg_dump/pg_dump.c
+94-46Lines changed: 94 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,7 @@
5555
#include "catalog/pg_trigger_d.h"
5656
#include "catalog/pg_type_d.h"
5757
#include "common/connect.h"
58+
#include "common/int.h"
5859
#include "common/relpath.h"
5960
#include "compress_io.h"
6061
#include "dumputils.h"
@@ -92,6 +93,17 @@ typedef struct
9293
int objsubid; /* subobject (table column #) */
9394
} SecLabelItem;
9495

96+
typedef struct
97+
{
98+
Oid oid; /* object OID */
99+
char relkind; /* object kind */
100+
RelFileNumber relfilenumber; /* object filenode */
101+
Oid toast_oid; /* toast table OID */
102+
RelFileNumber toast_relfilenumber; /* toast table filenode */
103+
Oid toast_index_oid; /* toast table index OID */
104+
RelFileNumber toast_index_relfilenumber; /* toast table index filenode */
105+
} BinaryUpgradeClassOidItem;
106+
95107
typedef enum OidOptions
96108
{
97109
zeroIsError = 1,
@@ -157,6 +169,10 @@ static int ncomments = 0;
157169
static SecLabelItem *seclabels = NULL;
158170
static int nseclabels = 0;
159171

172+
/* sorted table of pg_class information for binary upgrade */
173+
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
174+
static int nbinaryUpgradeClassOids = 0;
175+
160176
/*
161177
* The default number of rows per INSERT when
162178
* --inserts is specified without --rows-per-insert
@@ -322,6 +338,7 @@ static void binary_upgrade_set_type_oids_by_type_oid(Archive *fout,
322338
static void binary_upgrade_set_type_oids_by_rel(Archive *fout,
323339
PQExpBuffer upgrade_buffer,
324340
const TableInfo *tbinfo);
341+
static void collectBinaryUpgradeClassOids(Archive *fout);
325342
static void binary_upgrade_set_pg_class_oids(Archive *fout,
326343
PQExpBuffer upgrade_buffer,
327344
Oid pg_class_oid);
@@ -971,6 +988,10 @@ main(int argc, char **argv)
971988
if (!dopt.no_security_labels)
972989
collectSecLabels(fout);
973990

991+
/* For binary upgrade mode, collect required pg_class information. */
992+
if (dopt.binary_upgrade)
993+
collectBinaryUpgradeClassOids(fout);
994+
974995
/* Lastly, create dummy objects to represent the section boundaries */
975996
boundaryObjs = createBoundaryObjects();
976997

@@ -5383,18 +5404,67 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
53835404
pg_type_oid, false, false);
53845405
}
53855406

5407+
/*
5408+
* bsearch() comparator for BinaryUpgradeClassOidItem
5409+
*/
5410+
static int
5411+
BinaryUpgradeClassOidItemCmp(const void *p1, const void *p2)
5412+
{
5413+
BinaryUpgradeClassOidItem v1 = *((const BinaryUpgradeClassOidItem *) p1);
5414+
BinaryUpgradeClassOidItem v2 = *((const BinaryUpgradeClassOidItem *) p2);
5415+
5416+
return pg_cmp_u32(v1.oid, v2.oid);
5417+
}
5418+
5419+
/*
5420+
* collectBinaryUpgradeClassOids
5421+
*
5422+
* Construct a table of pg_class information required for
5423+
* binary_upgrade_set_pg_class_oids(). The table is sorted by OID for speed in
5424+
* lookup.
5425+
*/
5426+
static void
5427+
collectBinaryUpgradeClassOids(Archive *fout)
5428+
{
5429+
PGresult *res;
5430+
const char *query;
5431+
5432+
query = "SELECT c.oid, c.relkind, c.relfilenode, c.reltoastrelid, "
5433+
"ct.relfilenode, i.indexrelid, cti.relfilenode "
5434+
"FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index i "
5435+
"ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
5436+
"LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
5437+
"LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
5438+
"ORDER BY c.oid;";
5439+
5440+
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
5441+
5442+
nbinaryUpgradeClassOids = PQntuples(res);
5443+
binaryUpgradeClassOids = (BinaryUpgradeClassOidItem *)
5444+
pg_malloc(nbinaryUpgradeClassOids * sizeof(BinaryUpgradeClassOidItem));
5445+
5446+
for (int i = 0; i < nbinaryUpgradeClassOids; i++)
5447+
{
5448+
binaryUpgradeClassOids[i].oid = atooid(PQgetvalue(res, i, 0));
5449+
binaryUpgradeClassOids[i].relkind = *PQgetvalue(res, i, 1);
5450+
binaryUpgradeClassOids[i].relfilenumber = atooid(PQgetvalue(res, i, 2));
5451+
binaryUpgradeClassOids[i].toast_oid = atooid(PQgetvalue(res, i, 3));
5452+
binaryUpgradeClassOids[i].toast_relfilenumber = atooid(PQgetvalue(res, i, 4));
5453+
binaryUpgradeClassOids[i].toast_index_oid = atooid(PQgetvalue(res, i, 5));
5454+
binaryUpgradeClassOids[i].toast_index_relfilenumber = atooid(PQgetvalue(res, i, 6));
5455+
}
5456+
5457+
PQclear(res);
5458+
}
5459+
53865460
static void
53875461
binary_upgrade_set_pg_class_oids(Archive *fout,
53885462
PQExpBuffer upgrade_buffer, Oid pg_class_oid)
53895463
{
5390-
PQExpBuffer upgrade_query = createPQExpBuffer();
5391-
PGresult *upgrade_res;
5392-
RelFileNumber relfilenumber;
5393-
Oid toast_oid;
5394-
RelFileNumber toast_relfilenumber;
5395-
char relkind;
5396-
Oid toast_index_oid;
5397-
RelFileNumber toast_index_relfilenumber;
5464+
BinaryUpgradeClassOidItem key = {0};
5465+
BinaryUpgradeClassOidItem *entry;
5466+
5467+
Assert(binaryUpgradeClassOids);
53985468

53995469
/*
54005470
* Preserve the OID and relfilenumber of the table, table's index, table's
@@ -5407,35 +5477,16 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
54075477
* by the new backend, so we can copy the files during binary upgrade
54085478
* without worrying about this case.
54095479
*/
5410-
appendPQExpBuffer(upgrade_query,
5411-
"SELECT c.relkind, c.relfilenode, c.reltoastrelid, ct.relfilenode AS toast_relfilenode, i.indexrelid, cti.relfilenode AS toast_index_relfilenode "
5412-
"FROM pg_catalog.pg_class c LEFT JOIN "
5413-
"pg_catalog.pg_index i ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
5414-
"LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
5415-
"LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
5416-
"WHERE c.oid = '%u'::pg_catalog.oid;",
5417-
pg_class_oid);
5418-
5419-
upgrade_res = ExecuteSqlQueryForSingleRow(fout, upgrade_query->data);
5420-
5421-
relkind = *PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "relkind"));
5422-
5423-
relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
5424-
PQfnumber(upgrade_res, "relfilenode")));
5425-
toast_oid = atooid(PQgetvalue(upgrade_res, 0,
5426-
PQfnumber(upgrade_res, "reltoastrelid")));
5427-
toast_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
5428-
PQfnumber(upgrade_res, "toast_relfilenode")));
5429-
toast_index_oid = atooid(PQgetvalue(upgrade_res, 0,
5430-
PQfnumber(upgrade_res, "indexrelid")));
5431-
toast_index_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
5432-
PQfnumber(upgrade_res, "toast_index_relfilenode")));
5480+
key.oid = pg_class_oid;
5481+
entry = bsearch(&key, binaryUpgradeClassOids, nbinaryUpgradeClassOids,
5482+
sizeof(BinaryUpgradeClassOidItem),
5483+
BinaryUpgradeClassOidItemCmp);
54335484

54345485
appendPQExpBufferStr(upgrade_buffer,
54355486
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
54365487

5437-
if (relkind != RELKIND_INDEX &&
5438-
relkind != RELKIND_PARTITIONED_INDEX)
5488+
if (entry->relkind != RELKIND_INDEX &&
5489+
entry->relkind != RELKIND_PARTITIONED_INDEX)
54395490
{
54405491
appendPQExpBuffer(upgrade_buffer,
54415492
"SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('%u'::pg_catalog.oid);\n",
@@ -5446,32 +5497,33 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
54465497
* partitioned tables have a relfilenumber, which should not be
54475498
* preserved when upgrading.
54485499
*/
5449-
if (RelFileNumberIsValid(relfilenumber) && relkind != RELKIND_PARTITIONED_TABLE)
5500+
if (RelFileNumberIsValid(entry->relfilenumber) &&
5501+
entry->relkind != RELKIND_PARTITIONED_TABLE)
54505502
appendPQExpBuffer(upgrade_buffer,
54515503
"SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
5452-
relfilenumber);
5504+
entry->relfilenumber);
54535505

54545506
/*
54555507
* In a pre-v12 database, partitioned tables might be marked as having
54565508
* toast tables, but we should ignore them if so.
54575509
*/
5458-
if (OidIsValid(toast_oid) &&
5459-
relkind != RELKIND_PARTITIONED_TABLE)
5510+
if (OidIsValid(entry->toast_oid) &&
5511+
entry->relkind != RELKIND_PARTITIONED_TABLE)
54605512
{
54615513
appendPQExpBuffer(upgrade_buffer,
54625514
"SELECT pg_catalog.binary_upgrade_set_next_toast_pg_class_oid('%u'::pg_catalog.oid);\n",
5463-
toast_oid);
5515+
entry->toast_oid);
54645516
appendPQExpBuffer(upgrade_buffer,
54655517
"SELECT pg_catalog.binary_upgrade_set_next_toast_relfilenode('%u'::pg_catalog.oid);\n",
5466-
toast_relfilenumber);
5518+
entry->toast_relfilenumber);
54675519

54685520
/* every toast table has an index */
54695521
appendPQExpBuffer(upgrade_buffer,
54705522
"SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('%u'::pg_catalog.oid);\n",
5471-
toast_index_oid);
5523+
entry->toast_index_oid);
54725524
appendPQExpBuffer(upgrade_buffer,
54735525
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
5474-
toast_index_relfilenumber);
5526+
entry->toast_index_relfilenumber);
54755527
}
54765528
}
54775529
else
@@ -5482,14 +5534,10 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
54825534
pg_class_oid);
54835535
appendPQExpBuffer(upgrade_buffer,
54845536
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
5485-
relfilenumber);
5537+
entry->relfilenumber);
54865538
}
54875539

5488-
PQclear(upgrade_res);
5489-
54905540
appendPQExpBufferChar(upgrade_buffer, '\n');
5491-
5492-
destroyPQExpBuffer(upgrade_query);
54935541
}
54945542

54955543
/*

‎src/tools/pgindent/typedefs.list

Copy file name to clipboardExpand all lines: src/tools/pgindent/typedefs.list
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -253,6 +253,7 @@ BernoulliSamplerData
253253
BgWorkerStartTime
254254
BgwHandleStatus
255255
BinaryArithmFunc
256+
BinaryUpgradeClassOidItem
256257
BindParamCbData
257258
BipartiteMatchState
258259
BitString

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.