improve performance of pg_dump --binary-upgrade
Started by Nathan Bossartover 1 year ago12 messages
While examining pg_upgrade on a cluster with many tables (created with the
command in [0]/messages/by-id/3612876.1689443232@sss.pgh.pa.us), I noticed that a huge amount of pg_dump time goes towards
the binary_upgrade_set_pg_class_oids() function. This function executes a
rather expensive query for a single row, and this function appears to be
called for most of the rows in pg_class.
The attached work-in-progress patch speeds up 'pg_dump --binary-upgrade'
for this case. Instead of executing the query in every call to the
function, we can execute it once during the first call and store all the
required information in a sorted array that we can bsearch() in future
calls. For the aformentioned test, pg_dump on my machine goes from ~2
minutes to ~18 seconds, which is much closer to the ~14 seconds it takes
without --binary-upgrade.
One downside of this approach is the memory usage. This was more-or-less
the first approach that crossed my mind, so I wouldn't be surprised if
there's a better way. I tried to keep the pg_dump output the same, but if
that isn't important, maybe we could dump all the pg_class OIDs at once
instead of calling binary_upgrade_set_pg_class_oids() for each one.
[0]: /messages/by-id/3612876.1689443232@sss.pgh.pa.us
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
v1-0001-Improve-performance-of-pg_dump-binary-upgrade.patchtext/x-diff; charset=us-asciiDownload
From 27b4a3249dd97376f13a7c99505330ab7cd78e3f Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 17 Apr 2024 22:55:27 -0500
Subject: [PATCH v1 1/1] Improve performance of pg_dump --binary-upgrade.
---
src/bin/pg_dump/pg_dump.c | 113 +++++++++++++++++++------------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 70 insertions(+), 44 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c52e961b30..d93d974108 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -55,6 +55,7 @@
#include "catalog/pg_trigger_d.h"
#include "catalog/pg_type_d.h"
#include "common/connect.h"
+#include "common/int.h"
#include "common/relpath.h"
#include "compress_io.h"
#include "dumputils.h"
@@ -99,6 +100,17 @@ typedef enum OidOptions
zeroAsNone = 4,
} OidOptions;
+typedef struct
+{
+ Oid oid;
+ char relkind;
+ RelFileNumber relfilenode;
+ Oid reltoastrelid;
+ RelFileNumber toast_relfilenode;
+ Oid indexrelid;
+ RelFileNumber toast_index_relfilenode;
+} BinaryUpgradeClassOids;
+
/* global decls */
static bool dosync = true; /* Issue fsync() to make dump durable on disk. */
@@ -5392,19 +5404,56 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
pg_type_oid, false, false);
}
+static int
+BinaryUpgradeClassOidsCmp(const void *p1, const void *p2)
+{
+ BinaryUpgradeClassOids v1 = *((const BinaryUpgradeClassOids *) p1);
+ BinaryUpgradeClassOids v2 = *((const BinaryUpgradeClassOids *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
static void
binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer, Oid pg_class_oid,
bool is_index)
{
- PQExpBuffer upgrade_query = createPQExpBuffer();
- PGresult *upgrade_res;
- RelFileNumber relfilenumber;
- Oid toast_oid;
- RelFileNumber toast_relfilenumber;
- char relkind;
- Oid toast_index_oid;
- RelFileNumber toast_index_relfilenumber;
+ static BinaryUpgradeClassOids *oids = NULL;
+ static int oids_len = 0;
+ BinaryUpgradeClassOids key = {0, '?', 0, 0, 0, 0, 0};
+ BinaryUpgradeClassOids *entry;
+
+ if (oids == NULL)
+ {
+ PGresult *res;
+
+ res = ExecuteSqlQuery(fout,
+ "SELECT c.oid, c.relkind, c.relfilenode, c.reltoastrelid, ct.relfilenode AS toast_relfilenode, "
+ "i.indexrelid, cti.relfilenode AS toast_index_relfilenode "
+ "FROM pg_catalog.pg_class c LEFT JOIN "
+ "pg_catalog.pg_index i ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
+ "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
+ "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
+ "ORDER BY c.oid;",
+ PGRES_TUPLES_OK);
+
+ oids_len = PQntuples(res);
+ oids = (BinaryUpgradeClassOids *)
+ pg_malloc(oids_len * sizeof(BinaryUpgradeClassOids));
+
+ for (int i = 0; i < oids_len; i++)
+ {
+ oids[i].oid = atooid(PQgetvalue(res, i, 0));
+ oids[i].relkind = *PQgetvalue(res, i, 1);
+ oids[i].relfilenode = atooid(PQgetvalue(res, i, 2));
+ oids[i].reltoastrelid = atooid(PQgetvalue(res, i, 3));
+ oids[i].toast_relfilenode = atooid(PQgetvalue(res, i, 4));
+ oids[i].indexrelid = atooid(PQgetvalue(res, i, 5));
+ oids[i].toast_index_relfilenode = atooid(PQgetvalue(res, i, 6));
+ }
+
+ PQclear(res);
+ }
/*
* Preserve the OID and relfilenumber of the table, table's index, table's
@@ -5417,29 +5466,9 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* by the new backend, so we can copy the files during binary upgrade
* without worrying about this case.
*/
- appendPQExpBuffer(upgrade_query,
- "SELECT c.relkind, c.relfilenode, c.reltoastrelid, ct.relfilenode AS toast_relfilenode, i.indexrelid, cti.relfilenode AS toast_index_relfilenode "
- "FROM pg_catalog.pg_class c LEFT JOIN "
- "pg_catalog.pg_index i ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
- "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
- "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
- "WHERE c.oid = '%u'::pg_catalog.oid;",
- pg_class_oid);
-
- upgrade_res = ExecuteSqlQueryForSingleRow(fout, upgrade_query->data);
-
- relkind = *PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "relkind"));
-
- relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "relfilenode")));
- toast_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "reltoastrelid")));
- toast_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_relfilenode")));
- toast_index_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "indexrelid")));
- toast_index_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_index_relfilenode")));
+ key.oid = pg_class_oid;
+ entry = bsearch(&key, oids, oids_len, sizeof(BinaryUpgradeClassOids),
+ BinaryUpgradeClassOidsCmp);
appendPQExpBufferStr(upgrade_buffer,
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
@@ -5455,35 +5484,33 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* partitioned tables have a relfilenumber, which should not be
* preserved when upgrading.
*/
- if (RelFileNumberIsValid(relfilenumber) && relkind != RELKIND_PARTITIONED_TABLE)
+ if (RelFileNumberIsValid(entry->relfilenode) && entry->relkind != RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
/*
* In a pre-v12 database, partitioned tables might be marked as having
* toast tables, but we should ignore them if so.
*/
- if (OidIsValid(toast_oid) &&
- relkind != RELKIND_PARTITIONED_TABLE)
+ if (OidIsValid(entry->reltoastrelid) &&
+ entry->relkind != RELKIND_PARTITIONED_TABLE)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_oid);
+ entry->reltoastrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_relfilenode('%u'::pg_catalog.oid);\n",
- toast_relfilenumber);
+ entry->toast_relfilenode);
/* every toast table has an index */
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_index_oid);
+ entry->indexrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- toast_index_relfilenumber);
+ entry->toast_index_relfilenode);
}
-
- PQclear(upgrade_res);
}
else
{
@@ -5493,12 +5520,10 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
pg_class_oid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
}
appendPQExpBufferChar(upgrade_buffer, '\n');
-
- destroyPQExpBuffer(upgrade_query);
}
/*
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index d551ada325..c8062a1a20 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -255,6 +255,7 @@ BernoulliSamplerData
BgWorkerStartTime
BgwHandleStatus
BinaryArithmFunc
+BinaryUpgradeClassOids
BindParamCbData
BipartiteMatchState
BitString
--
2.25.1
Re: improve performance of pg_dump --binary-upgrade
One downside of this approach is the memory usage. This was more-or-less
Bar-napkin math tells me in a worst-case architecture and braindead byte
alignment, we'd burn 64 bytes per struct, so the 100K tables cited would be
about 6.25MB of memory.
The obvious low-memory alternative would be to make a prepared statement,
though that does nothing to cut down on the roundtrips.
I think this is a good trade off.
Re: improve performance of pg_dump --binary-upgrade
On Thu, Apr 18, 2024 at 02:08:28AM -0400, Corey Huinker wrote:
Bar-napkin math tells me in a worst-case architecture and braindead byte
alignment, we'd burn 64 bytes per struct, so the 100K tables cited would be
about 6.25MB of memory.The obvious low-memory alternative would be to make a prepared statement,
though that does nothing to cut down on the roundtrips.I think this is a good trade off.
I've not checked the patch in details or tested it, but caching this
information to gain this speed sounds like a very good thing.
--
Michael
Re: improve performance of pg_dump --binary-upgrade
On 18 Apr 2024, at 06:17, Nathan Bossart <nathandbossart@gmail.com> wrote:
The attached work-in-progress patch speeds up 'pg_dump --binary-upgrade'
for this case. Instead of executing the query in every call to the
function, we can execute it once during the first call and store all the
required information in a sorted array that we can bsearch() in future
calls.
That does indeed seem like a saner approach. Since we look up the relkind we
can also remove the is_index parameter to binary_upgrade_set_pg_class_oids
since we already know that without the caller telling us?
One downside of this approach is the memory usage.
I'm not too worried about the worst-case performance of this.
This was more-or-less
the first approach that crossed my mind, so I wouldn't be surprised if
there's a better way. I tried to keep the pg_dump output the same, but if
that isn't important, maybe we could dump all the pg_class OIDs at once
instead of calling binary_upgrade_set_pg_class_oids() for each one.
Without changing the backend handling of the Oid's we can't really do that
AFAICT, the backend stores the Oid for the next call so it needs to be per
relation like now?
For Greenplum we moved this to the backend by first dumping all Oids which were
read into backend cache, and during relation creation the Oid to use was looked
up in the backend. (This wasn't a performance change, it was to allow multiple
shared-nothing clusters to have a unified view of Oids, so I never benchmarked
it all that well.) The upside of that is that the magic Oid variables in the
backend can be removed, but it obviously adds slight overhead in others.
--
Daniel Gustafsson
Re: improve performance of pg_dump --binary-upgrade
On Thu, Apr 18, 2024 at 02:08:28AM -0400, Corey Huinker wrote:
Bar-napkin math tells me in a worst-case architecture and braindead byte
alignment, we'd burn 64 bytes per struct, so the 100K tables cited would be
about 6.25MB of memory.
That doesn't seem too terrible.
The obvious low-memory alternative would be to make a prepared statement,
though that does nothing to cut down on the roundtrips.I think this is a good trade off.
Cool.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Re: improve performance of pg_dump --binary-upgrade
On Thu, Apr 18, 2024 at 09:24:53AM +0200, Daniel Gustafsson wrote:
On 18 Apr 2024, at 06:17, Nathan Bossart <nathandbossart@gmail.com> wrote:
The attached work-in-progress patch speeds up 'pg_dump --binary-upgrade'
for this case. Instead of executing the query in every call to the
function, we can execute it once during the first call and store all the
required information in a sorted array that we can bsearch() in future
calls.That does indeed seem like a saner approach. Since we look up the relkind we
can also remove the is_index parameter to binary_upgrade_set_pg_class_oids
since we already know that without the caller telling us?
Yeah. It looks like that's been possible since commit 9a974cb, so I can
write a prerequisite patch for this.
One downside of this approach is the memory usage.
I'm not too worried about the worst-case performance of this.
Cool. That seems to be the general sentiment.
This was more-or-less
the first approach that crossed my mind, so I wouldn't be surprised if
there's a better way. I tried to keep the pg_dump output the same, but if
that isn't important, maybe we could dump all the pg_class OIDs at once
instead of calling binary_upgrade_set_pg_class_oids() for each one.Without changing the backend handling of the Oid's we can't really do that
AFAICT, the backend stores the Oid for the next call so it needs to be per
relation like now?
Right, this would require additional changes.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Re: improve performance of pg_dump --binary-upgrade
On Thu, Apr 18, 2024 at 10:23:01AM -0500, Nathan Bossart wrote:
On Thu, Apr 18, 2024 at 09:24:53AM +0200, Daniel Gustafsson wrote:
That does indeed seem like a saner approach. Since we look up the relkind we
can also remove the is_index parameter to binary_upgrade_set_pg_class_oids
since we already know that without the caller telling us?Yeah. It looks like that's been possible since commit 9a974cb, so I can
write a prerequisite patch for this.
Here's a new patch set with this change.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
v2-0001-Remove-is_index-parameter-from-binary_upgrade_set.patchtext/x-diff; charset=us-asciiDownload
From d74692fe52d3c376d4b60323dd367a36593cd31b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 18 Apr 2024 15:15:19 -0500
Subject: [PATCH v2 1/2] Remove is_index parameter from
binary_upgrade_set_pg_class_oids().
---
src/bin/pg_dump/pg_dump.c | 20 ++++++++++----------
1 file changed, 10 insertions(+), 10 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 6d2f3fdef3..58c77a5e2b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -324,7 +324,7 @@ static void binary_upgrade_set_type_oids_by_rel(Archive *fout,
const TableInfo *tbinfo);
static void binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer,
- Oid pg_class_oid, bool is_index);
+ Oid pg_class_oid);
static void binary_upgrade_extension_member(PQExpBuffer upgrade_buffer,
const DumpableObject *dobj,
const char *objtype,
@@ -5394,8 +5394,7 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
static void
binary_upgrade_set_pg_class_oids(Archive *fout,
- PQExpBuffer upgrade_buffer, Oid pg_class_oid,
- bool is_index)
+ PQExpBuffer upgrade_buffer, Oid pg_class_oid)
{
PQExpBuffer upgrade_query = createPQExpBuffer();
PGresult *upgrade_res;
@@ -5444,7 +5443,8 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
appendPQExpBufferStr(upgrade_buffer,
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
- if (!is_index)
+ if (relkind != RELKIND_INDEX &&
+ relkind != RELKIND_PARTITIONED_INDEX)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('%u'::pg_catalog.oid);\n",
@@ -11864,7 +11864,7 @@ dumpCompositeType(Archive *fout, const TypeInfo *tyinfo)
binary_upgrade_set_type_oids_by_type_oid(fout, q,
tyinfo->dobj.catId.oid,
false, false);
- binary_upgrade_set_pg_class_oids(fout, q, tyinfo->typrelid, false);
+ binary_upgrade_set_pg_class_oids(fout, q, tyinfo->typrelid);
}
qtypname = pg_strdup(fmtId(tyinfo->dobj.name));
@@ -15998,7 +15998,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
appendPQExpBuffer(q, "CREATE VIEW %s", qualrelname);
@@ -16100,7 +16100,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
appendPQExpBuffer(q, "CREATE %s%s %s",
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
@@ -16990,7 +16990,7 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- indxinfo->dobj.catId.oid, true);
+ indxinfo->dobj.catId.oid);
/* Plain secondary index */
appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
@@ -17244,7 +17244,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- indxinfo->dobj.catId.oid, true);
+ indxinfo->dobj.catId.oid);
appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s\n", foreign,
fmtQualifiedDumpable(tbinfo));
@@ -17653,7 +17653,7 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
{
binary_upgrade_set_pg_class_oids(fout, query,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
/*
* In older PG versions a sequence will have a pg_type entry, but v14
--
2.25.1
v2-0002-Improve-performance-of-pg_dump-binary-upgrade.patchtext/x-diff; charset=us-asciiDownload
From 1580a7b9896b727925cab364ae9fdc7107d791d4 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 17 Apr 2024 22:55:27 -0500
Subject: [PATCH v2 2/2] Improve performance of pg_dump --binary-upgrade.
---
src/bin/pg_dump/pg_dump.c | 117 +++++++++++++++++++------------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 72 insertions(+), 46 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 58c77a5e2b..062638ad34 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -55,6 +55,7 @@
#include "catalog/pg_trigger_d.h"
#include "catalog/pg_type_d.h"
#include "common/connect.h"
+#include "common/int.h"
#include "common/relpath.h"
#include "compress_io.h"
#include "dumputils.h"
@@ -99,6 +100,17 @@ typedef enum OidOptions
zeroAsNone = 4,
} OidOptions;
+typedef struct
+{
+ Oid oid;
+ char relkind;
+ RelFileNumber relfilenode;
+ Oid reltoastrelid;
+ RelFileNumber toast_relfilenode;
+ Oid indexrelid;
+ RelFileNumber toast_index_relfilenode;
+} BinaryUpgradeClassOids;
+
/* global decls */
static bool dosync = true; /* Issue fsync() to make dump durable on disk. */
@@ -5392,18 +5404,55 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
pg_type_oid, false, false);
}
+static int
+BinaryUpgradeClassOidsCmp(const void *p1, const void *p2)
+{
+ BinaryUpgradeClassOids v1 = *((const BinaryUpgradeClassOids *) p1);
+ BinaryUpgradeClassOids v2 = *((const BinaryUpgradeClassOids *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
static void
binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer, Oid pg_class_oid)
{
- PQExpBuffer upgrade_query = createPQExpBuffer();
- PGresult *upgrade_res;
- RelFileNumber relfilenumber;
- Oid toast_oid;
- RelFileNumber toast_relfilenumber;
- char relkind;
- Oid toast_index_oid;
- RelFileNumber toast_index_relfilenumber;
+ static BinaryUpgradeClassOids *oids = NULL;
+ static int oids_len = 0;
+ BinaryUpgradeClassOids key = {0, '?', 0, 0, 0, 0, 0};
+ BinaryUpgradeClassOids *entry;
+
+ if (oids == NULL)
+ {
+ PGresult *res;
+
+ res = ExecuteSqlQuery(fout,
+ "SELECT c.oid, c.relkind, c.relfilenode, c.reltoastrelid, ct.relfilenode AS toast_relfilenode, "
+ "i.indexrelid, cti.relfilenode AS toast_index_relfilenode "
+ "FROM pg_catalog.pg_class c LEFT JOIN "
+ "pg_catalog.pg_index i ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
+ "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
+ "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
+ "ORDER BY c.oid;",
+ PGRES_TUPLES_OK);
+
+ oids_len = PQntuples(res);
+ oids = (BinaryUpgradeClassOids *)
+ pg_malloc(oids_len * sizeof(BinaryUpgradeClassOids));
+
+ for (int i = 0; i < oids_len; i++)
+ {
+ oids[i].oid = atooid(PQgetvalue(res, i, 0));
+ oids[i].relkind = *PQgetvalue(res, i, 1);
+ oids[i].relfilenode = atooid(PQgetvalue(res, i, 2));
+ oids[i].reltoastrelid = atooid(PQgetvalue(res, i, 3));
+ oids[i].toast_relfilenode = atooid(PQgetvalue(res, i, 4));
+ oids[i].indexrelid = atooid(PQgetvalue(res, i, 5));
+ oids[i].toast_index_relfilenode = atooid(PQgetvalue(res, i, 6));
+ }
+
+ PQclear(res);
+ }
/*
* Preserve the OID and relfilenumber of the table, table's index, table's
@@ -5416,35 +5465,15 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* by the new backend, so we can copy the files during binary upgrade
* without worrying about this case.
*/
- appendPQExpBuffer(upgrade_query,
- "SELECT c.relkind, c.relfilenode, c.reltoastrelid, ct.relfilenode AS toast_relfilenode, i.indexrelid, cti.relfilenode AS toast_index_relfilenode "
- "FROM pg_catalog.pg_class c LEFT JOIN "
- "pg_catalog.pg_index i ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
- "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
- "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
- "WHERE c.oid = '%u'::pg_catalog.oid;",
- pg_class_oid);
-
- upgrade_res = ExecuteSqlQueryForSingleRow(fout, upgrade_query->data);
-
- relkind = *PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "relkind"));
-
- relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "relfilenode")));
- toast_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "reltoastrelid")));
- toast_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_relfilenode")));
- toast_index_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "indexrelid")));
- toast_index_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_index_relfilenode")));
+ key.oid = pg_class_oid;
+ entry = bsearch(&key, oids, oids_len, sizeof(BinaryUpgradeClassOids),
+ BinaryUpgradeClassOidsCmp);
appendPQExpBufferStr(upgrade_buffer,
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
- if (relkind != RELKIND_INDEX &&
- relkind != RELKIND_PARTITIONED_INDEX)
+ if (entry->relkind != RELKIND_INDEX &&
+ entry->relkind != RELKIND_PARTITIONED_INDEX)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('%u'::pg_catalog.oid);\n",
@@ -5455,35 +5484,33 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* partitioned tables have a relfilenumber, which should not be
* preserved when upgrading.
*/
- if (RelFileNumberIsValid(relfilenumber) && relkind != RELKIND_PARTITIONED_TABLE)
+ if (RelFileNumberIsValid(entry->relfilenode) && entry->relkind != RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
/*
* In a pre-v12 database, partitioned tables might be marked as having
* toast tables, but we should ignore them if so.
*/
- if (OidIsValid(toast_oid) &&
- relkind != RELKIND_PARTITIONED_TABLE)
+ if (OidIsValid(entry->reltoastrelid) &&
+ entry->relkind != RELKIND_PARTITIONED_TABLE)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_oid);
+ entry->reltoastrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_relfilenode('%u'::pg_catalog.oid);\n",
- toast_relfilenumber);
+ entry->toast_relfilenode);
/* every toast table has an index */
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_index_oid);
+ entry->indexrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- toast_index_relfilenumber);
+ entry->toast_index_relfilenode);
}
-
- PQclear(upgrade_res);
}
else
{
@@ -5493,12 +5520,10 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
pg_class_oid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
}
appendPQExpBufferChar(upgrade_buffer, '\n');
-
- destroyPQExpBuffer(upgrade_query);
}
/*
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index d551ada325..c8062a1a20 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -255,6 +255,7 @@ BernoulliSamplerData
BgWorkerStartTime
BgwHandleStatus
BinaryArithmFunc
+BinaryUpgradeClassOids
BindParamCbData
BipartiteMatchState
BitString
--
2.25.1
Re: improve performance of pg_dump --binary-upgrade
On 18 Apr 2024, at 22:28, Nathan Bossart <nathandbossart@gmail.com> wrote:
On Thu, Apr 18, 2024 at 10:23:01AM -0500, Nathan Bossart wrote:
On Thu, Apr 18, 2024 at 09:24:53AM +0200, Daniel Gustafsson wrote:
That does indeed seem like a saner approach. Since we look up the relkind we
can also remove the is_index parameter to binary_upgrade_set_pg_class_oids
since we already know that without the caller telling us?Yeah. It looks like that's been possible since commit 9a974cb, so I can
write a prerequisite patch for this.Here's a new patch set with this change.
From a read-through they look good, a very nice performance improvement in an
important path. I think it would be nice with some comments on the
BinaryUpgradeClassOids struct (since the code using it is thousands of lines
away), and a comment on the if (oids == NULL) block explaining the caching.
--
Daniel Gustafsson
Re: improve performance of pg_dump --binary-upgrade
On Thu, Apr 18, 2024 at 10:33:08PM +0200, Daniel Gustafsson wrote:
From a read-through they look good, a very nice performance improvement in an
important path. I think it would be nice with some comments on the
BinaryUpgradeClassOids struct (since the code using it is thousands of lines
away), and a comment on the if (oids == NULL) block explaining the caching.
Added. Thanks for reviewing! Unfortunately, this one will have to sit for
a couple months...
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
v3-0001-Remove-is_index-parameter-from-binary_upgrade_set.patchtext/x-diff; charset=us-asciiDownload
From 6797e4e1fc1a63f710ed0a409b1337880eb91ad4 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 18 Apr 2024 15:15:19 -0500
Subject: [PATCH v3 1/2] Remove is_index parameter from
binary_upgrade_set_pg_class_oids().
---
src/bin/pg_dump/pg_dump.c | 20 ++++++++++----------
1 file changed, 10 insertions(+), 10 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 6d2f3fdef3..58c77a5e2b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -324,7 +324,7 @@ static void binary_upgrade_set_type_oids_by_rel(Archive *fout,
const TableInfo *tbinfo);
static void binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer,
- Oid pg_class_oid, bool is_index);
+ Oid pg_class_oid);
static void binary_upgrade_extension_member(PQExpBuffer upgrade_buffer,
const DumpableObject *dobj,
const char *objtype,
@@ -5394,8 +5394,7 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
static void
binary_upgrade_set_pg_class_oids(Archive *fout,
- PQExpBuffer upgrade_buffer, Oid pg_class_oid,
- bool is_index)
+ PQExpBuffer upgrade_buffer, Oid pg_class_oid)
{
PQExpBuffer upgrade_query = createPQExpBuffer();
PGresult *upgrade_res;
@@ -5444,7 +5443,8 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
appendPQExpBufferStr(upgrade_buffer,
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
- if (!is_index)
+ if (relkind != RELKIND_INDEX &&
+ relkind != RELKIND_PARTITIONED_INDEX)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('%u'::pg_catalog.oid);\n",
@@ -11864,7 +11864,7 @@ dumpCompositeType(Archive *fout, const TypeInfo *tyinfo)
binary_upgrade_set_type_oids_by_type_oid(fout, q,
tyinfo->dobj.catId.oid,
false, false);
- binary_upgrade_set_pg_class_oids(fout, q, tyinfo->typrelid, false);
+ binary_upgrade_set_pg_class_oids(fout, q, tyinfo->typrelid);
}
qtypname = pg_strdup(fmtId(tyinfo->dobj.name));
@@ -15998,7 +15998,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
appendPQExpBuffer(q, "CREATE VIEW %s", qualrelname);
@@ -16100,7 +16100,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
appendPQExpBuffer(q, "CREATE %s%s %s",
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
@@ -16990,7 +16990,7 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- indxinfo->dobj.catId.oid, true);
+ indxinfo->dobj.catId.oid);
/* Plain secondary index */
appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
@@ -17244,7 +17244,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- indxinfo->dobj.catId.oid, true);
+ indxinfo->dobj.catId.oid);
appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s\n", foreign,
fmtQualifiedDumpable(tbinfo));
@@ -17653,7 +17653,7 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
{
binary_upgrade_set_pg_class_oids(fout, query,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
/*
* In older PG versions a sequence will have a pg_type entry, but v14
--
2.25.1
v3-0002-Improve-performance-of-pg_dump-binary-upgrade.patchtext/x-diff; charset=us-asciiDownload
From b3ae9df69fdbf386d09250f1c225ecd8665141a7 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 17 Apr 2024 22:55:27 -0500
Subject: [PATCH v3 2/2] Improve performance of pg_dump --binary-upgrade.
---
src/bin/pg_dump/pg_dump.c | 126 ++++++++++++++++++++-----------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 81 insertions(+), 46 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 58c77a5e2b..b1f06a199b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -55,6 +55,7 @@
#include "catalog/pg_trigger_d.h"
#include "catalog/pg_type_d.h"
#include "common/connect.h"
+#include "common/int.h"
#include "common/relpath.h"
#include "compress_io.h"
#include "dumputils.h"
@@ -99,6 +100,21 @@ typedef enum OidOptions
zeroAsNone = 4,
} OidOptions;
+/*
+ * This struct is used for the sorted array populated by
+ * binary_upgrade_set_pg_class_oids().
+ */
+typedef struct
+{
+ Oid oid;
+ char relkind;
+ RelFileNumber relfilenode;
+ Oid reltoastrelid;
+ RelFileNumber toast_relfilenode;
+ Oid indexrelid;
+ RelFileNumber toast_index_relfilenode;
+} BinaryUpgradeClassOids;
+
/* global decls */
static bool dosync = true; /* Issue fsync() to make dump durable on disk. */
@@ -5392,18 +5408,60 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
pg_type_oid, false, false);
}
+static int
+BinaryUpgradeClassOidsCmp(const void *p1, const void *p2)
+{
+ BinaryUpgradeClassOids v1 = *((const BinaryUpgradeClassOids *) p1);
+ BinaryUpgradeClassOids v2 = *((const BinaryUpgradeClassOids *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
static void
binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer, Oid pg_class_oid)
{
- PQExpBuffer upgrade_query = createPQExpBuffer();
- PGresult *upgrade_res;
- RelFileNumber relfilenumber;
- Oid toast_oid;
- RelFileNumber toast_relfilenumber;
- char relkind;
- Oid toast_index_oid;
- RelFileNumber toast_index_relfilenumber;
+ static BinaryUpgradeClassOids *oids = NULL;
+ static int oids_len = 0;
+ BinaryUpgradeClassOids key = {0, '?', 0, 0, 0, 0, 0};
+ BinaryUpgradeClassOids *entry;
+
+ /*
+ * If this is the first call to this function, gather all the information
+ * we'll need for this call and future calls into a sorted array. This
+ * allows us to avoid executing this expensive query many times.
+ */
+ if (oids == NULL)
+ {
+ PGresult *res;
+
+ res = ExecuteSqlQuery(fout,
+ "SELECT c.oid, c.relkind, c.relfilenode, c.reltoastrelid, ct.relfilenode AS toast_relfilenode, "
+ "i.indexrelid, cti.relfilenode AS toast_index_relfilenode "
+ "FROM pg_catalog.pg_class c LEFT JOIN "
+ "pg_catalog.pg_index i ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
+ "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
+ "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
+ "ORDER BY c.oid;",
+ PGRES_TUPLES_OK);
+
+ oids_len = PQntuples(res);
+ oids = (BinaryUpgradeClassOids *)
+ pg_malloc(oids_len * sizeof(BinaryUpgradeClassOids));
+
+ for (int i = 0; i < oids_len; i++)
+ {
+ oids[i].oid = atooid(PQgetvalue(res, i, 0));
+ oids[i].relkind = *PQgetvalue(res, i, 1);
+ oids[i].relfilenode = atooid(PQgetvalue(res, i, 2));
+ oids[i].reltoastrelid = atooid(PQgetvalue(res, i, 3));
+ oids[i].toast_relfilenode = atooid(PQgetvalue(res, i, 4));
+ oids[i].indexrelid = atooid(PQgetvalue(res, i, 5));
+ oids[i].toast_index_relfilenode = atooid(PQgetvalue(res, i, 6));
+ }
+
+ PQclear(res);
+ }
/*
* Preserve the OID and relfilenumber of the table, table's index, table's
@@ -5416,35 +5474,15 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* by the new backend, so we can copy the files during binary upgrade
* without worrying about this case.
*/
- appendPQExpBuffer(upgrade_query,
- "SELECT c.relkind, c.relfilenode, c.reltoastrelid, ct.relfilenode AS toast_relfilenode, i.indexrelid, cti.relfilenode AS toast_index_relfilenode "
- "FROM pg_catalog.pg_class c LEFT JOIN "
- "pg_catalog.pg_index i ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
- "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
- "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
- "WHERE c.oid = '%u'::pg_catalog.oid;",
- pg_class_oid);
-
- upgrade_res = ExecuteSqlQueryForSingleRow(fout, upgrade_query->data);
-
- relkind = *PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "relkind"));
-
- relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "relfilenode")));
- toast_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "reltoastrelid")));
- toast_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_relfilenode")));
- toast_index_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "indexrelid")));
- toast_index_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_index_relfilenode")));
+ key.oid = pg_class_oid;
+ entry = bsearch(&key, oids, oids_len, sizeof(BinaryUpgradeClassOids),
+ BinaryUpgradeClassOidsCmp);
appendPQExpBufferStr(upgrade_buffer,
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
- if (relkind != RELKIND_INDEX &&
- relkind != RELKIND_PARTITIONED_INDEX)
+ if (entry->relkind != RELKIND_INDEX &&
+ entry->relkind != RELKIND_PARTITIONED_INDEX)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('%u'::pg_catalog.oid);\n",
@@ -5455,35 +5493,33 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* partitioned tables have a relfilenumber, which should not be
* preserved when upgrading.
*/
- if (RelFileNumberIsValid(relfilenumber) && relkind != RELKIND_PARTITIONED_TABLE)
+ if (RelFileNumberIsValid(entry->relfilenode) && entry->relkind != RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
/*
* In a pre-v12 database, partitioned tables might be marked as having
* toast tables, but we should ignore them if so.
*/
- if (OidIsValid(toast_oid) &&
- relkind != RELKIND_PARTITIONED_TABLE)
+ if (OidIsValid(entry->reltoastrelid) &&
+ entry->relkind != RELKIND_PARTITIONED_TABLE)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_oid);
+ entry->reltoastrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_relfilenode('%u'::pg_catalog.oid);\n",
- toast_relfilenumber);
+ entry->toast_relfilenode);
/* every toast table has an index */
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_index_oid);
+ entry->indexrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- toast_index_relfilenumber);
+ entry->toast_index_relfilenode);
}
-
- PQclear(upgrade_res);
}
else
{
@@ -5493,12 +5529,10 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
pg_class_oid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
}
appendPQExpBufferChar(upgrade_buffer, '\n');
-
- destroyPQExpBuffer(upgrade_query);
}
/*
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index d551ada325..c8062a1a20 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -255,6 +255,7 @@ BernoulliSamplerData
BgWorkerStartTime
BgwHandleStatus
BinaryArithmFunc
+BinaryUpgradeClassOids
BindParamCbData
BipartiteMatchState
BitString
--
2.25.1
Re: improve performance of pg_dump --binary-upgrade
I noticed that there are some existing examples of this sort of thing in
pg_dump (e.g., commit d5e8930), so I adjusted the patch to match the
surrounding style a bit better.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
v4-0001-Remove-is_index-parameter-from-binary_upgrade_set.patchtext/x-diff; charset=us-asciiDownload
From 80dd3233730422298ffe3b4523a7c58d7e9b55b9 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 18 Apr 2024 15:15:19 -0500
Subject: [PATCH v4 1/2] Remove is_index parameter from
binary_upgrade_set_pg_class_oids().
---
src/bin/pg_dump/pg_dump.c | 20 ++++++++++----------
1 file changed, 10 insertions(+), 10 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ed9bab3bfe..bbdb0628c9 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -324,7 +324,7 @@ static void binary_upgrade_set_type_oids_by_rel(Archive *fout,
const TableInfo *tbinfo);
static void binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer,
- Oid pg_class_oid, bool is_index);
+ Oid pg_class_oid);
static void binary_upgrade_extension_member(PQExpBuffer upgrade_buffer,
const DumpableObject *dobj,
const char *objtype,
@@ -5394,8 +5394,7 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
static void
binary_upgrade_set_pg_class_oids(Archive *fout,
- PQExpBuffer upgrade_buffer, Oid pg_class_oid,
- bool is_index)
+ PQExpBuffer upgrade_buffer, Oid pg_class_oid)
{
PQExpBuffer upgrade_query = createPQExpBuffer();
PGresult *upgrade_res;
@@ -5444,7 +5443,8 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
appendPQExpBufferStr(upgrade_buffer,
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
- if (!is_index)
+ if (relkind != RELKIND_INDEX &&
+ relkind != RELKIND_PARTITIONED_INDEX)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('%u'::pg_catalog.oid);\n",
@@ -11878,7 +11878,7 @@ dumpCompositeType(Archive *fout, const TypeInfo *tyinfo)
binary_upgrade_set_type_oids_by_type_oid(fout, q,
tyinfo->dobj.catId.oid,
false, false);
- binary_upgrade_set_pg_class_oids(fout, q, tyinfo->typrelid, false);
+ binary_upgrade_set_pg_class_oids(fout, q, tyinfo->typrelid);
}
qtypname = pg_strdup(fmtId(tyinfo->dobj.name));
@@ -16012,7 +16012,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
appendPQExpBuffer(q, "CREATE VIEW %s", qualrelname);
@@ -16114,7 +16114,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
appendPQExpBuffer(q, "CREATE %s%s %s",
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
@@ -17005,7 +17005,7 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- indxinfo->dobj.catId.oid, true);
+ indxinfo->dobj.catId.oid);
/* Plain secondary index */
appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
@@ -17259,7 +17259,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- indxinfo->dobj.catId.oid, true);
+ indxinfo->dobj.catId.oid);
appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s\n", foreign,
fmtQualifiedDumpable(tbinfo));
@@ -17668,7 +17668,7 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
{
binary_upgrade_set_pg_class_oids(fout, query,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
/*
* In older PG versions a sequence will have a pg_type entry, but v14
--
2.25.1
v4-0002-Improve-performance-of-pg_dump-binary-upgrade.patchtext/x-diff; charset=us-asciiDownload
From 14da726b675e4dd3e69f0b75f256b2757e22b900 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 22 Apr 2024 13:21:18 -0500
Subject: [PATCH v4 2/2] Improve performance of pg_dump --binary-upgrade.
---
src/bin/pg_dump/pg_dump.c | 141 +++++++++++++++++++++----------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 96 insertions(+), 46 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bbdb0628c9..cad391dec1 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -55,6 +55,7 @@
#include "catalog/pg_trigger_d.h"
#include "catalog/pg_type_d.h"
#include "common/connect.h"
+#include "common/int.h"
#include "common/relpath.h"
#include "compress_io.h"
#include "dumputils.h"
@@ -92,6 +93,17 @@ typedef struct
int objsubid; /* subobject (table column #) */
} SecLabelItem;
+typedef struct
+{
+ Oid oid; /* object OID */
+ char relkind; /* object kind */
+ RelFileNumber relfilenode; /* object filenode */
+ Oid reltoastrelid; /* toast table OID */
+ RelFileNumber toast_relfilenode; /* toast table filenode */
+ Oid indexrelid; /* toast table index OID */
+ RelFileNumber toast_index_relfilenode; /* toast table index filenode */
+} BinaryUpgradeClassOidItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -157,6 +169,10 @@ static int ncomments = 0;
static SecLabelItem *seclabels = NULL;
static int nseclabels = 0;
+/* sorted table of pg_class information for binary upgrade */
+static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
+static int nbinaryUpgradeClassOids = 0;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -322,6 +338,7 @@ static void binary_upgrade_set_type_oids_by_type_oid(Archive *fout,
static void binary_upgrade_set_type_oids_by_rel(Archive *fout,
PQExpBuffer upgrade_buffer,
const TableInfo *tbinfo);
+static void collectBinaryUpgradeClassOids(Archive *fout);
static void binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer,
Oid pg_class_oid);
@@ -971,6 +988,10 @@ main(int argc, char **argv)
if (!dopt.no_security_labels)
collectSecLabels(fout);
+ /* For binary upgrade mode, collect required pg_class information. */
+ if (dopt.binary_upgrade)
+ collectBinaryUpgradeClassOids(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -5392,18 +5413,68 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
pg_type_oid, false, false);
}
+/*
+ * bsearch() comparator for BinaryUpgradeClassOidItem
+ */
+static int
+BinaryUpgradeClassOidItemCmp(const void *p1, const void *p2)
+{
+ BinaryUpgradeClassOidItem v1 = *((const BinaryUpgradeClassOidItem *) p1);
+ BinaryUpgradeClassOidItem v2 = *((const BinaryUpgradeClassOidItem *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectBinaryUpgradeClassOids
+ *
+ * Construct a table of pg_class information required for
+ * binary_upgrade_set_pg_class_oids(). The table is sorted by OID for speed in
+ * lookup.
+ */
+static void
+collectBinaryUpgradeClassOids(Archive *fout)
+{
+ PGresult *res;
+ const char *query;
+
+ query = "SELECT c.oid, c.relkind, c.relfilenode, c.reltoastrelid, "
+ "ct.relfilenode AS toast_relfilenode, i.indexrelid, "
+ "cti.relfilenode AS toast_index_relfilenode "
+ "FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index i "
+ "ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
+ "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
+ "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
+ "ORDER BY c.oid;";
+
+ res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+ nbinaryUpgradeClassOids = PQntuples(res);
+ binaryUpgradeClassOids = (BinaryUpgradeClassOidItem *)
+ pg_malloc(nbinaryUpgradeClassOids * sizeof(BinaryUpgradeClassOidItem));
+
+ for (int i = 0; i < nbinaryUpgradeClassOids; i++)
+ {
+ binaryUpgradeClassOids[i].oid = atooid(PQgetvalue(res, i, 0));
+ binaryUpgradeClassOids[i].relkind = *PQgetvalue(res, i, 1);
+ binaryUpgradeClassOids[i].relfilenode = atooid(PQgetvalue(res, i, 2));
+ binaryUpgradeClassOids[i].reltoastrelid = atooid(PQgetvalue(res, i, 3));
+ binaryUpgradeClassOids[i].toast_relfilenode = atooid(PQgetvalue(res, i, 4));
+ binaryUpgradeClassOids[i].indexrelid = atooid(PQgetvalue(res, i, 5));
+ binaryUpgradeClassOids[i].toast_index_relfilenode = atooid(PQgetvalue(res, i, 6));
+ }
+
+ PQclear(res);
+}
+
static void
binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer, Oid pg_class_oid)
{
- PQExpBuffer upgrade_query = createPQExpBuffer();
- PGresult *upgrade_res;
- RelFileNumber relfilenumber;
- Oid toast_oid;
- RelFileNumber toast_relfilenumber;
- char relkind;
- Oid toast_index_oid;
- RelFileNumber toast_index_relfilenumber;
+ BinaryUpgradeClassOidItem key = {0};
+ BinaryUpgradeClassOidItem *entry;
+
+ Assert(binaryUpgradeClassOids);
/*
* Preserve the OID and relfilenumber of the table, table's index, table's
@@ -5416,35 +5487,16 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* by the new backend, so we can copy the files during binary upgrade
* without worrying about this case.
*/
- appendPQExpBuffer(upgrade_query,
- "SELECT c.relkind, c.relfilenode, c.reltoastrelid, ct.relfilenode AS toast_relfilenode, i.indexrelid, cti.relfilenode AS toast_index_relfilenode "
- "FROM pg_catalog.pg_class c LEFT JOIN "
- "pg_catalog.pg_index i ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
- "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
- "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
- "WHERE c.oid = '%u'::pg_catalog.oid;",
- pg_class_oid);
-
- upgrade_res = ExecuteSqlQueryForSingleRow(fout, upgrade_query->data);
-
- relkind = *PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "relkind"));
-
- relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "relfilenode")));
- toast_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "reltoastrelid")));
- toast_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_relfilenode")));
- toast_index_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "indexrelid")));
- toast_index_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_index_relfilenode")));
+ key.oid = pg_class_oid;
+ entry = bsearch(&key, binaryUpgradeClassOids, nbinaryUpgradeClassOids,
+ sizeof(BinaryUpgradeClassOidItem),
+ BinaryUpgradeClassOidItemCmp);
appendPQExpBufferStr(upgrade_buffer,
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
- if (relkind != RELKIND_INDEX &&
- relkind != RELKIND_PARTITIONED_INDEX)
+ if (entry->relkind != RELKIND_INDEX &&
+ entry->relkind != RELKIND_PARTITIONED_INDEX)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('%u'::pg_catalog.oid);\n",
@@ -5455,35 +5507,34 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* partitioned tables have a relfilenumber, which should not be
* preserved when upgrading.
*/
- if (RelFileNumberIsValid(relfilenumber) && relkind != RELKIND_PARTITIONED_TABLE)
+ if (RelFileNumberIsValid(entry->relfilenode) &&
+ entry->relkind != RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
/*
* In a pre-v12 database, partitioned tables might be marked as having
* toast tables, but we should ignore them if so.
*/
- if (OidIsValid(toast_oid) &&
- relkind != RELKIND_PARTITIONED_TABLE)
+ if (OidIsValid(entry->reltoastrelid) &&
+ entry->relkind != RELKIND_PARTITIONED_TABLE)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_oid);
+ entry->reltoastrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_relfilenode('%u'::pg_catalog.oid);\n",
- toast_relfilenumber);
+ entry->toast_relfilenode);
/* every toast table has an index */
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_index_oid);
+ entry->indexrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- toast_index_relfilenumber);
+ entry->toast_index_relfilenode);
}
-
- PQclear(upgrade_res);
}
else
{
@@ -5493,12 +5544,10 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
pg_class_oid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
}
appendPQExpBufferChar(upgrade_buffer, '\n');
-
- destroyPQExpBuffer(upgrade_query);
}
/*
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index d551ada325..6a90a79f7e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -255,6 +255,7 @@ BernoulliSamplerData
BgWorkerStartTime
BgwHandleStatus
BinaryArithmFunc
+BinaryUpgradeClassOidItem
BindParamCbData
BipartiteMatchState
BitString
--
2.25.1
Re: improve performance of pg_dump --binary-upgrade
rebased
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
v5-0001-Remove-is_index-parameter-from-binary_upgrade_set.patchtext/x-diff; charset=us-asciiDownload
From ded5e61ff631c2d02835fdba941068dcd86741ce Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 18 Apr 2024 15:15:19 -0500
Subject: [PATCH v5 1/2] Remove is_index parameter from
binary_upgrade_set_pg_class_oids().
---
src/bin/pg_dump/pg_dump.c | 20 ++++++++++----------
1 file changed, 10 insertions(+), 10 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index e324070828..0fbb8e8831 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -324,7 +324,7 @@ static void binary_upgrade_set_type_oids_by_rel(Archive *fout,
const TableInfo *tbinfo);
static void binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer,
- Oid pg_class_oid, bool is_index);
+ Oid pg_class_oid);
static void binary_upgrade_extension_member(PQExpBuffer upgrade_buffer,
const DumpableObject *dobj,
const char *objtype,
@@ -5391,8 +5391,7 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
static void
binary_upgrade_set_pg_class_oids(Archive *fout,
- PQExpBuffer upgrade_buffer, Oid pg_class_oid,
- bool is_index)
+ PQExpBuffer upgrade_buffer, Oid pg_class_oid)
{
PQExpBuffer upgrade_query = createPQExpBuffer();
PGresult *upgrade_res;
@@ -5441,7 +5440,8 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
appendPQExpBufferStr(upgrade_buffer,
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
- if (!is_index)
+ if (relkind != RELKIND_INDEX &&
+ relkind != RELKIND_PARTITIONED_INDEX)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('%u'::pg_catalog.oid);\n",
@@ -11668,7 +11668,7 @@ dumpCompositeType(Archive *fout, const TypeInfo *tyinfo)
binary_upgrade_set_type_oids_by_type_oid(fout, q,
tyinfo->dobj.catId.oid,
false, false);
- binary_upgrade_set_pg_class_oids(fout, q, tyinfo->typrelid, false);
+ binary_upgrade_set_pg_class_oids(fout, q, tyinfo->typrelid);
}
qtypname = pg_strdup(fmtId(tyinfo->dobj.name));
@@ -15802,7 +15802,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
appendPQExpBuffer(q, "CREATE VIEW %s", qualrelname);
@@ -15904,7 +15904,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
appendPQExpBuffer(q, "CREATE %s%s %s",
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
@@ -16755,7 +16755,7 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- indxinfo->dobj.catId.oid, true);
+ indxinfo->dobj.catId.oid);
/* Plain secondary index */
appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
@@ -17009,7 +17009,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
- indxinfo->dobj.catId.oid, true);
+ indxinfo->dobj.catId.oid);
appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s\n", foreign,
fmtQualifiedDumpable(tbinfo));
@@ -17403,7 +17403,7 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
if (dopt->binary_upgrade)
{
binary_upgrade_set_pg_class_oids(fout, query,
- tbinfo->dobj.catId.oid, false);
+ tbinfo->dobj.catId.oid);
/*
* In older PG versions a sequence will have a pg_type entry, but v14
--
2.25.1
v5-0002-Improve-performance-of-pg_dump-binary-upgrade.patchtext/x-diff; charset=us-asciiDownload
From 7ff5168f5984865bd405e5d53dc6a190f989e7cd Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 22 Apr 2024 13:21:18 -0500
Subject: [PATCH v5 2/2] Improve performance of pg_dump --binary-upgrade.
---
src/bin/pg_dump/pg_dump.c | 141 +++++++++++++++++++++----------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 96 insertions(+), 46 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 0fbb8e8831..7b8ddc6443 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -55,6 +55,7 @@
#include "catalog/pg_trigger_d.h"
#include "catalog/pg_type_d.h"
#include "common/connect.h"
+#include "common/int.h"
#include "common/relpath.h"
#include "compress_io.h"
#include "dumputils.h"
@@ -92,6 +93,17 @@ typedef struct
int objsubid; /* subobject (table column #) */
} SecLabelItem;
+typedef struct
+{
+ Oid oid; /* object OID */
+ char relkind; /* object kind */
+ RelFileNumber relfilenode; /* object filenode */
+ Oid reltoastrelid; /* toast table OID */
+ RelFileNumber toast_relfilenode; /* toast table filenode */
+ Oid indexrelid; /* toast table index OID */
+ RelFileNumber toast_index_relfilenode; /* toast table index filenode */
+} BinaryUpgradeClassOidItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -157,6 +169,10 @@ static int ncomments = 0;
static SecLabelItem *seclabels = NULL;
static int nseclabels = 0;
+/* sorted table of pg_class information for binary upgrade */
+static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
+static int nbinaryUpgradeClassOids = 0;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -322,6 +338,7 @@ static void binary_upgrade_set_type_oids_by_type_oid(Archive *fout,
static void binary_upgrade_set_type_oids_by_rel(Archive *fout,
PQExpBuffer upgrade_buffer,
const TableInfo *tbinfo);
+static void collectBinaryUpgradeClassOids(Archive *fout);
static void binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer,
Oid pg_class_oid);
@@ -971,6 +988,10 @@ main(int argc, char **argv)
if (!dopt.no_security_labels)
collectSecLabels(fout);
+ /* For binary upgrade mode, collect required pg_class information. */
+ if (dopt.binary_upgrade)
+ collectBinaryUpgradeClassOids(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -5389,18 +5410,68 @@ binary_upgrade_set_type_oids_by_rel(Archive *fout,
pg_type_oid, false, false);
}
+/*
+ * bsearch() comparator for BinaryUpgradeClassOidItem
+ */
+static int
+BinaryUpgradeClassOidItemCmp(const void *p1, const void *p2)
+{
+ BinaryUpgradeClassOidItem v1 = *((const BinaryUpgradeClassOidItem *) p1);
+ BinaryUpgradeClassOidItem v2 = *((const BinaryUpgradeClassOidItem *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectBinaryUpgradeClassOids
+ *
+ * Construct a table of pg_class information required for
+ * binary_upgrade_set_pg_class_oids(). The table is sorted by OID for speed in
+ * lookup.
+ */
+static void
+collectBinaryUpgradeClassOids(Archive *fout)
+{
+ PGresult *res;
+ const char *query;
+
+ query = "SELECT c.oid, c.relkind, c.relfilenode, c.reltoastrelid, "
+ "ct.relfilenode AS toast_relfilenode, i.indexrelid, "
+ "cti.relfilenode AS toast_index_relfilenode "
+ "FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index i "
+ "ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
+ "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
+ "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
+ "ORDER BY c.oid;";
+
+ res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+ nbinaryUpgradeClassOids = PQntuples(res);
+ binaryUpgradeClassOids = (BinaryUpgradeClassOidItem *)
+ pg_malloc(nbinaryUpgradeClassOids * sizeof(BinaryUpgradeClassOidItem));
+
+ for (int i = 0; i < nbinaryUpgradeClassOids; i++)
+ {
+ binaryUpgradeClassOids[i].oid = atooid(PQgetvalue(res, i, 0));
+ binaryUpgradeClassOids[i].relkind = *PQgetvalue(res, i, 1);
+ binaryUpgradeClassOids[i].relfilenode = atooid(PQgetvalue(res, i, 2));
+ binaryUpgradeClassOids[i].reltoastrelid = atooid(PQgetvalue(res, i, 3));
+ binaryUpgradeClassOids[i].toast_relfilenode = atooid(PQgetvalue(res, i, 4));
+ binaryUpgradeClassOids[i].indexrelid = atooid(PQgetvalue(res, i, 5));
+ binaryUpgradeClassOids[i].toast_index_relfilenode = atooid(PQgetvalue(res, i, 6));
+ }
+
+ PQclear(res);
+}
+
static void
binary_upgrade_set_pg_class_oids(Archive *fout,
PQExpBuffer upgrade_buffer, Oid pg_class_oid)
{
- PQExpBuffer upgrade_query = createPQExpBuffer();
- PGresult *upgrade_res;
- RelFileNumber relfilenumber;
- Oid toast_oid;
- RelFileNumber toast_relfilenumber;
- char relkind;
- Oid toast_index_oid;
- RelFileNumber toast_index_relfilenumber;
+ BinaryUpgradeClassOidItem key = {0};
+ BinaryUpgradeClassOidItem *entry;
+
+ Assert(binaryUpgradeClassOids);
/*
* Preserve the OID and relfilenumber of the table, table's index, table's
@@ -5413,35 +5484,16 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* by the new backend, so we can copy the files during binary upgrade
* without worrying about this case.
*/
- appendPQExpBuffer(upgrade_query,
- "SELECT c.relkind, c.relfilenode, c.reltoastrelid, ct.relfilenode AS toast_relfilenode, i.indexrelid, cti.relfilenode AS toast_index_relfilenode "
- "FROM pg_catalog.pg_class c LEFT JOIN "
- "pg_catalog.pg_index i ON (c.reltoastrelid = i.indrelid AND i.indisvalid) "
- "LEFT JOIN pg_catalog.pg_class ct ON (c.reltoastrelid = ct.oid) "
- "LEFT JOIN pg_catalog.pg_class AS cti ON (i.indexrelid = cti.oid) "
- "WHERE c.oid = '%u'::pg_catalog.oid;",
- pg_class_oid);
-
- upgrade_res = ExecuteSqlQueryForSingleRow(fout, upgrade_query->data);
-
- relkind = *PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "relkind"));
-
- relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "relfilenode")));
- toast_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "reltoastrelid")));
- toast_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_relfilenode")));
- toast_index_oid = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "indexrelid")));
- toast_index_relfilenumber = atooid(PQgetvalue(upgrade_res, 0,
- PQfnumber(upgrade_res, "toast_index_relfilenode")));
+ key.oid = pg_class_oid;
+ entry = bsearch(&key, binaryUpgradeClassOids, nbinaryUpgradeClassOids,
+ sizeof(BinaryUpgradeClassOidItem),
+ BinaryUpgradeClassOidItemCmp);
appendPQExpBufferStr(upgrade_buffer,
"\n-- For binary upgrade, must preserve pg_class oids and relfilenodes\n");
- if (relkind != RELKIND_INDEX &&
- relkind != RELKIND_PARTITIONED_INDEX)
+ if (entry->relkind != RELKIND_INDEX &&
+ entry->relkind != RELKIND_PARTITIONED_INDEX)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('%u'::pg_catalog.oid);\n",
@@ -5452,32 +5504,33 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
* partitioned tables have a relfilenumber, which should not be
* preserved when upgrading.
*/
- if (RelFileNumberIsValid(relfilenumber) && relkind != RELKIND_PARTITIONED_TABLE)
+ if (RelFileNumberIsValid(entry->relfilenode) &&
+ entry->relkind != RELKIND_PARTITIONED_TABLE)
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
/*
* In a pre-v12 database, partitioned tables might be marked as having
* toast tables, but we should ignore them if so.
*/
- if (OidIsValid(toast_oid) &&
- relkind != RELKIND_PARTITIONED_TABLE)
+ if (OidIsValid(entry->reltoastrelid) &&
+ entry->relkind != RELKIND_PARTITIONED_TABLE)
{
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_oid);
+ entry->reltoastrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_toast_relfilenode('%u'::pg_catalog.oid);\n",
- toast_relfilenumber);
+ entry->toast_relfilenode);
/* every toast table has an index */
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('%u'::pg_catalog.oid);\n",
- toast_index_oid);
+ entry->indexrelid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- toast_index_relfilenumber);
+ entry->toast_index_relfilenode);
}
}
else
@@ -5488,14 +5541,10 @@ binary_upgrade_set_pg_class_oids(Archive *fout,
pg_class_oid);
appendPQExpBuffer(upgrade_buffer,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
- relfilenumber);
+ entry->relfilenode);
}
- PQclear(upgrade_res);
-
appendPQExpBufferChar(upgrade_buffer, '\n');
-
- destroyPQExpBuffer(upgrade_query);
}
/*
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 85445bbf9a..c691f85ad0 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -253,6 +253,7 @@ BernoulliSamplerData
BgWorkerStartTime
BgwHandleStatus
BinaryArithmFunc
+BinaryUpgradeClassOidItem
BindParamCbData
BipartiteMatchState
BitString
--
2.25.1