public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
To: [email protected]
Subject: pg_dump: optimize dumpFunc()
Date: Thu, 1 Aug 2024 11:52:57 -0500
Message-ID: <Zqu9aaYtNyHj8V7m@nathan> (raw)
I've recently committed some optimizations for dumping sequences and
pg_class information (commits 68e9629, bd15b7d, and 2329cad), and I noticed
that we are also executing a query per function in pg_dump. Commit be85727
optimized this by preparing the query ahead of time, but I found that we
can improve performance further by gathering all the relevant data in a
single query. Here are the results I see for a database with 10k simple
functions with and without the attached patch:
with patch:
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.04s user 0.01s system 40% cpu 0.118 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.04s user 0.01s system 41% cpu 0.107 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.04s user 0.01s system 42% cpu 0.103 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.04s user 0.01s system 44% cpu 0.105 total
without patch:
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.05s user 0.03s system 32% cpu 0.253 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.05s user 0.03s system 32% cpu 0.252 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.06s user 0.03s system 32% cpu 0.251 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.06s user 0.03s system 33% cpu 0.254 total
This one looks a little different than the sequence/pg_class commits. Much
of the function information isn't terribly conducive to parsing into
fixed-size variables in an array, so instead I've opted to just leave the
PGresult around for reference by dumpFunc(). This patch also creates an
ordered array of function OIDs to speed up locating the relevant index in
the PGresult for use in calls to PQgetvalue().
I may be running out of opportunities where this style of optimization
makes much difference. I'll likely start focusing on the restore side
soon.
--
nathan
From da388f00c57ebc743e9229f0f306b074d35b0be5 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 31 Jul 2024 16:47:44 -0500
Subject: [PATCH v1 1/1] optimize dumpFunc()
---
src/bin/pg_dump/pg_backup.h | 1 -
src/bin/pg_dump/pg_dump.c | 232 ++++++++++++++++++++----------------
2 files changed, 132 insertions(+), 101 deletions(-)
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index fbf5f1c515..c1e496ee71 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -68,7 +68,6 @@ enum _dumpPreparedQueries
PREPQUERY_DUMPCOMPOSITETYPE,
PREPQUERY_DUMPDOMAIN,
PREPQUERY_DUMPENUMTYPE,
- PREPQUERY_DUMPFUNC,
PREPQUERY_DUMPOPR,
PREPQUERY_DUMPRANGETYPE,
PREPQUERY_DUMPTABLEATTACH,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 79190470f7..6297ac0599 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -209,6 +209,10 @@ static int nbinaryUpgradeClassOids = 0;
static SequenceItem *sequences = NULL;
static int nsequences = 0;
+/* functions */
+static Oid *funcoids = NULL;
+static PGresult *funcs = NULL;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -289,6 +293,7 @@ static void dumpCompositeTypeColComments(Archive *fout, const TypeInfo *tyinfo,
PGresult *res);
static void dumpShellType(Archive *fout, const ShellTypeInfo *stinfo);
static void dumpProcLang(Archive *fout, const ProcLangInfo *plang);
+static void collectFuncs(Archive *fout);
static void dumpFunc(Archive *fout, const FuncInfo *finfo);
static void dumpCast(Archive *fout, const CastInfo *cast);
static void dumpTransform(Archive *fout, const TransformInfo *transform);
@@ -1032,6 +1037,10 @@ main(int argc, char **argv)
/* Collect sequence information. */
collectSequences(fout);
+ /* Collect function information. */
+ if (!dopt.dataOnly)
+ collectFuncs(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -12065,6 +12074,101 @@ format_function_signature(Archive *fout, const FuncInfo *finfo, bool honor_quote
return fn.data;
}
+/*
+ * bsearch() comparator for Oid
+ */
+static int
+OidCmp(const void *p1, const void *p2)
+{
+ Oid o1 = *((const Oid *) p1);
+ Oid o2 = *((const Oid *) p2);
+
+ return pg_cmp_u32(o1, o2);
+}
+
+/*
+ * collectFuncs
+ *
+ * Obtain function metadata and construct an ordered array of function OIDs for
+ * use by dumpFunc() to quickly find the index of a function entry.
+ */
+static void
+collectFuncs(Archive *fout)
+{
+ PQExpBuffer query = createPQExpBuffer();
+
+ Assert(!funcs);
+
+ appendPQExpBufferStr(query,
+ "SELECT\n"
+ "p.oid\n"
+ "proretset,\n"
+ "prosrc,\n"
+ "probin,\n"
+ "provolatile,\n"
+ "proisstrict,\n"
+ "prosecdef,\n"
+ "lanname,\n"
+ "proconfig,\n"
+ "procost,\n"
+ "prorows,\n"
+ "pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,\n"
+ "pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,\n"
+ "pg_catalog.pg_get_function_result(p.oid) AS funcresult,\n"
+ "proleakproof,\n");
+
+ if (fout->remoteVersion >= 90500)
+ appendPQExpBufferStr(query,
+ "array_to_string(protrftypes, ' ') AS protrftypes,\n");
+ else
+ appendPQExpBufferStr(query,
+ "NULL AS protrftypes,\n");
+
+ if (fout->remoteVersion >= 90600)
+ appendPQExpBufferStr(query,
+ "proparallel,\n");
+ else
+ appendPQExpBufferStr(query,
+ "'u' AS proparallel,\n");
+
+ if (fout->remoteVersion >= 110000)
+ appendPQExpBufferStr(query,
+ "prokind,\n");
+ else
+ appendPQExpBufferStr(query,
+ "CASE WHEN proiswindow THEN 'w' ELSE 'f' END AS prokind,\n");
+
+ if (fout->remoteVersion >= 120000)
+ appendPQExpBufferStr(query,
+ "prosupport,\n");
+ else
+ appendPQExpBufferStr(query,
+ "'-' AS prosupport,\n");
+
+ if (fout->remoteVersion >= 140000)
+ appendPQExpBufferStr(query,
+ "pg_get_function_sqlbody(p.oid) AS prosqlbody\n");
+ else
+ appendPQExpBufferStr(query,
+ "NULL AS prosqlbody\n");
+
+ appendPQExpBufferStr(query,
+ "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n"
+ "WHERE l.oid = p.prolang\n"
+ "ORDER BY p.oid");
+
+ funcs = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ funcoids = (Oid *) pg_malloc(PQntuples(funcs) * sizeof(Oid));
+
+ for (int i = 0; i < PQntuples(funcs); i++)
+ funcoids[i] = atooid(PQgetvalue(funcs, i, 0));
+
+ /*
+ * NB: we intentionally do not free the PGresult storage because
+ * dumpFunc() will reference it.
+ */
+ destroyPQExpBuffer(query);
+}
/*
* dumpFunc:
@@ -12074,11 +12178,9 @@ static void
dumpFunc(Archive *fout, const FuncInfo *finfo)
{
DumpOptions *dopt = fout->dopt;
- PQExpBuffer query;
PQExpBuffer q;
PQExpBuffer delqry;
PQExpBuffer asPart;
- PGresult *res;
char *funcsig; /* identity signature */
char *funcfullsig = NULL; /* full signature */
char *funcsig_tag;
@@ -12105,118 +12207,51 @@ dumpFunc(Archive *fout, const FuncInfo *finfo)
char **configitems = NULL;
int nconfigitems = 0;
const char *keyword;
+ Oid *funcoid;
+ int funcidx;
/* Do nothing in data-only dump */
if (dopt->dataOnly)
return;
- query = createPQExpBuffer();
+ Assert(funcs);
+
q = createPQExpBuffer();
delqry = createPQExpBuffer();
asPart = createPQExpBuffer();
- if (!fout->is_prepared[PREPQUERY_DUMPFUNC])
- {
- /* Set up query for function-specific details */
- appendPQExpBufferStr(query,
- "PREPARE dumpFunc(pg_catalog.oid) AS\n");
-
- appendPQExpBufferStr(query,
- "SELECT\n"
- "proretset,\n"
- "prosrc,\n"
- "probin,\n"
- "provolatile,\n"
- "proisstrict,\n"
- "prosecdef,\n"
- "lanname,\n"
- "proconfig,\n"
- "procost,\n"
- "prorows,\n"
- "pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,\n"
- "pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,\n"
- "pg_catalog.pg_get_function_result(p.oid) AS funcresult,\n"
- "proleakproof,\n");
-
- if (fout->remoteVersion >= 90500)
- appendPQExpBufferStr(query,
- "array_to_string(protrftypes, ' ') AS protrftypes,\n");
- else
- appendPQExpBufferStr(query,
- "NULL AS protrftypes,\n");
-
- if (fout->remoteVersion >= 90600)
- appendPQExpBufferStr(query,
- "proparallel,\n");
- else
- appendPQExpBufferStr(query,
- "'u' AS proparallel,\n");
+ funcoid = bsearch(&finfo->dobj.catId.oid, funcoids,
+ PQntuples(funcs), sizeof(Oid), OidCmp);
+ funcidx = funcoid - funcoids;
- if (fout->remoteVersion >= 110000)
- appendPQExpBufferStr(query,
- "prokind,\n");
- else
- appendPQExpBufferStr(query,
- "CASE WHEN proiswindow THEN 'w' ELSE 'f' END AS prokind,\n");
-
- if (fout->remoteVersion >= 120000)
- appendPQExpBufferStr(query,
- "prosupport,\n");
- else
- appendPQExpBufferStr(query,
- "'-' AS prosupport,\n");
-
- if (fout->remoteVersion >= 140000)
- appendPQExpBufferStr(query,
- "pg_get_function_sqlbody(p.oid) AS prosqlbody\n");
- else
- appendPQExpBufferStr(query,
- "NULL AS prosqlbody\n");
-
- appendPQExpBufferStr(query,
- "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n"
- "WHERE p.oid = $1 "
- "AND l.oid = p.prolang");
-
- ExecuteSqlStatement(fout, query->data);
-
- fout->is_prepared[PREPQUERY_DUMPFUNC] = true;
- }
-
- printfPQExpBuffer(query,
- "EXECUTE dumpFunc('%u')",
- finfo->dobj.catId.oid);
-
- res = ExecuteSqlQueryForSingleRow(fout, query->data);
-
- proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset"));
- if (PQgetisnull(res, 0, PQfnumber(res, "prosqlbody")))
+ proretset = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proretset"));
+ if (PQgetisnull(funcs, funcidx, PQfnumber(funcs, "prosqlbody")))
{
- prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc"));
- probin = PQgetvalue(res, 0, PQfnumber(res, "probin"));
+ prosrc = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prosrc"));
+ probin = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "probin"));
prosqlbody = NULL;
}
else
{
prosrc = NULL;
probin = NULL;
- prosqlbody = PQgetvalue(res, 0, PQfnumber(res, "prosqlbody"));
- }
- funcargs = PQgetvalue(res, 0, PQfnumber(res, "funcargs"));
- funciargs = PQgetvalue(res, 0, PQfnumber(res, "funciargs"));
- funcresult = PQgetvalue(res, 0, PQfnumber(res, "funcresult"));
- protrftypes = PQgetvalue(res, 0, PQfnumber(res, "protrftypes"));
- prokind = PQgetvalue(res, 0, PQfnumber(res, "prokind"));
- provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
- proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
- prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
- proleakproof = PQgetvalue(res, 0, PQfnumber(res, "proleakproof"));
- proconfig = PQgetvalue(res, 0, PQfnumber(res, "proconfig"));
- procost = PQgetvalue(res, 0, PQfnumber(res, "procost"));
- prorows = PQgetvalue(res, 0, PQfnumber(res, "prorows"));
- prosupport = PQgetvalue(res, 0, PQfnumber(res, "prosupport"));
- proparallel = PQgetvalue(res, 0, PQfnumber(res, "proparallel"));
- lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
+ prosqlbody = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prosqlbody"));
+ }
+ funcargs = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "funcargs"));
+ funciargs = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "funciargs"));
+ funcresult = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "funcresult"));
+ protrftypes = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "protrftypes"));
+ prokind = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prokind"));
+ provolatile = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "provolatile"));
+ proisstrict = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proisstrict"));
+ prosecdef = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prosecdef"));
+ proleakproof = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proleakproof"));
+ proconfig = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proconfig"));
+ procost = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "procost"));
+ prorows = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prorows"));
+ prosupport = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prosupport"));
+ proparallel = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proparallel"));
+ lanname = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "lanname"));
/*
* See backend/commands/functioncmds.c for details of how the 'AS' clause
@@ -12469,9 +12504,6 @@ dumpFunc(Archive *fout, const FuncInfo *finfo)
finfo->dobj.namespace->dobj.name,
NULL, finfo->rolname, &finfo->dacl);
- PQclear(res);
-
- destroyPQExpBuffer(query);
destroyPQExpBuffer(q);
destroyPQExpBuffer(delqry);
destroyPQExpBuffer(asPart);
--
2.39.3 (Apple Git-146)
Attachments:
[text/plain] v1-0001-optimize-dumpFunc.patch (10.7K, ../Zqu9aaYtNyHj8V7m@nathan/2-v1-0001-optimize-dumpFunc.patch)
download | inline diff:
From da388f00c57ebc743e9229f0f306b074d35b0be5 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 31 Jul 2024 16:47:44 -0500
Subject: [PATCH v1 1/1] optimize dumpFunc()
---
src/bin/pg_dump/pg_backup.h | 1 -
src/bin/pg_dump/pg_dump.c | 232 ++++++++++++++++++++----------------
2 files changed, 132 insertions(+), 101 deletions(-)
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index fbf5f1c515..c1e496ee71 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -68,7 +68,6 @@ enum _dumpPreparedQueries
PREPQUERY_DUMPCOMPOSITETYPE,
PREPQUERY_DUMPDOMAIN,
PREPQUERY_DUMPENUMTYPE,
- PREPQUERY_DUMPFUNC,
PREPQUERY_DUMPOPR,
PREPQUERY_DUMPRANGETYPE,
PREPQUERY_DUMPTABLEATTACH,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 79190470f7..6297ac0599 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -209,6 +209,10 @@ static int nbinaryUpgradeClassOids = 0;
static SequenceItem *sequences = NULL;
static int nsequences = 0;
+/* functions */
+static Oid *funcoids = NULL;
+static PGresult *funcs = NULL;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -289,6 +293,7 @@ static void dumpCompositeTypeColComments(Archive *fout, const TypeInfo *tyinfo,
PGresult *res);
static void dumpShellType(Archive *fout, const ShellTypeInfo *stinfo);
static void dumpProcLang(Archive *fout, const ProcLangInfo *plang);
+static void collectFuncs(Archive *fout);
static void dumpFunc(Archive *fout, const FuncInfo *finfo);
static void dumpCast(Archive *fout, const CastInfo *cast);
static void dumpTransform(Archive *fout, const TransformInfo *transform);
@@ -1032,6 +1037,10 @@ main(int argc, char **argv)
/* Collect sequence information. */
collectSequences(fout);
+ /* Collect function information. */
+ if (!dopt.dataOnly)
+ collectFuncs(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -12065,6 +12074,101 @@ format_function_signature(Archive *fout, const FuncInfo *finfo, bool honor_quote
return fn.data;
}
+/*
+ * bsearch() comparator for Oid
+ */
+static int
+OidCmp(const void *p1, const void *p2)
+{
+ Oid o1 = *((const Oid *) p1);
+ Oid o2 = *((const Oid *) p2);
+
+ return pg_cmp_u32(o1, o2);
+}
+
+/*
+ * collectFuncs
+ *
+ * Obtain function metadata and construct an ordered array of function OIDs for
+ * use by dumpFunc() to quickly find the index of a function entry.
+ */
+static void
+collectFuncs(Archive *fout)
+{
+ PQExpBuffer query = createPQExpBuffer();
+
+ Assert(!funcs);
+
+ appendPQExpBufferStr(query,
+ "SELECT\n"
+ "p.oid\n"
+ "proretset,\n"
+ "prosrc,\n"
+ "probin,\n"
+ "provolatile,\n"
+ "proisstrict,\n"
+ "prosecdef,\n"
+ "lanname,\n"
+ "proconfig,\n"
+ "procost,\n"
+ "prorows,\n"
+ "pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,\n"
+ "pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,\n"
+ "pg_catalog.pg_get_function_result(p.oid) AS funcresult,\n"
+ "proleakproof,\n");
+
+ if (fout->remoteVersion >= 90500)
+ appendPQExpBufferStr(query,
+ "array_to_string(protrftypes, ' ') AS protrftypes,\n");
+ else
+ appendPQExpBufferStr(query,
+ "NULL AS protrftypes,\n");
+
+ if (fout->remoteVersion >= 90600)
+ appendPQExpBufferStr(query,
+ "proparallel,\n");
+ else
+ appendPQExpBufferStr(query,
+ "'u' AS proparallel,\n");
+
+ if (fout->remoteVersion >= 110000)
+ appendPQExpBufferStr(query,
+ "prokind,\n");
+ else
+ appendPQExpBufferStr(query,
+ "CASE WHEN proiswindow THEN 'w' ELSE 'f' END AS prokind,\n");
+
+ if (fout->remoteVersion >= 120000)
+ appendPQExpBufferStr(query,
+ "prosupport,\n");
+ else
+ appendPQExpBufferStr(query,
+ "'-' AS prosupport,\n");
+
+ if (fout->remoteVersion >= 140000)
+ appendPQExpBufferStr(query,
+ "pg_get_function_sqlbody(p.oid) AS prosqlbody\n");
+ else
+ appendPQExpBufferStr(query,
+ "NULL AS prosqlbody\n");
+
+ appendPQExpBufferStr(query,
+ "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n"
+ "WHERE l.oid = p.prolang\n"
+ "ORDER BY p.oid");
+
+ funcs = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ funcoids = (Oid *) pg_malloc(PQntuples(funcs) * sizeof(Oid));
+
+ for (int i = 0; i < PQntuples(funcs); i++)
+ funcoids[i] = atooid(PQgetvalue(funcs, i, 0));
+
+ /*
+ * NB: we intentionally do not free the PGresult storage because
+ * dumpFunc() will reference it.
+ */
+ destroyPQExpBuffer(query);
+}
/*
* dumpFunc:
@@ -12074,11 +12178,9 @@ static void
dumpFunc(Archive *fout, const FuncInfo *finfo)
{
DumpOptions *dopt = fout->dopt;
- PQExpBuffer query;
PQExpBuffer q;
PQExpBuffer delqry;
PQExpBuffer asPart;
- PGresult *res;
char *funcsig; /* identity signature */
char *funcfullsig = NULL; /* full signature */
char *funcsig_tag;
@@ -12105,118 +12207,51 @@ dumpFunc(Archive *fout, const FuncInfo *finfo)
char **configitems = NULL;
int nconfigitems = 0;
const char *keyword;
+ Oid *funcoid;
+ int funcidx;
/* Do nothing in data-only dump */
if (dopt->dataOnly)
return;
- query = createPQExpBuffer();
+ Assert(funcs);
+
q = createPQExpBuffer();
delqry = createPQExpBuffer();
asPart = createPQExpBuffer();
- if (!fout->is_prepared[PREPQUERY_DUMPFUNC])
- {
- /* Set up query for function-specific details */
- appendPQExpBufferStr(query,
- "PREPARE dumpFunc(pg_catalog.oid) AS\n");
-
- appendPQExpBufferStr(query,
- "SELECT\n"
- "proretset,\n"
- "prosrc,\n"
- "probin,\n"
- "provolatile,\n"
- "proisstrict,\n"
- "prosecdef,\n"
- "lanname,\n"
- "proconfig,\n"
- "procost,\n"
- "prorows,\n"
- "pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,\n"
- "pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,\n"
- "pg_catalog.pg_get_function_result(p.oid) AS funcresult,\n"
- "proleakproof,\n");
-
- if (fout->remoteVersion >= 90500)
- appendPQExpBufferStr(query,
- "array_to_string(protrftypes, ' ') AS protrftypes,\n");
- else
- appendPQExpBufferStr(query,
- "NULL AS protrftypes,\n");
-
- if (fout->remoteVersion >= 90600)
- appendPQExpBufferStr(query,
- "proparallel,\n");
- else
- appendPQExpBufferStr(query,
- "'u' AS proparallel,\n");
+ funcoid = bsearch(&finfo->dobj.catId.oid, funcoids,
+ PQntuples(funcs), sizeof(Oid), OidCmp);
+ funcidx = funcoid - funcoids;
- if (fout->remoteVersion >= 110000)
- appendPQExpBufferStr(query,
- "prokind,\n");
- else
- appendPQExpBufferStr(query,
- "CASE WHEN proiswindow THEN 'w' ELSE 'f' END AS prokind,\n");
-
- if (fout->remoteVersion >= 120000)
- appendPQExpBufferStr(query,
- "prosupport,\n");
- else
- appendPQExpBufferStr(query,
- "'-' AS prosupport,\n");
-
- if (fout->remoteVersion >= 140000)
- appendPQExpBufferStr(query,
- "pg_get_function_sqlbody(p.oid) AS prosqlbody\n");
- else
- appendPQExpBufferStr(query,
- "NULL AS prosqlbody\n");
-
- appendPQExpBufferStr(query,
- "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n"
- "WHERE p.oid = $1 "
- "AND l.oid = p.prolang");
-
- ExecuteSqlStatement(fout, query->data);
-
- fout->is_prepared[PREPQUERY_DUMPFUNC] = true;
- }
-
- printfPQExpBuffer(query,
- "EXECUTE dumpFunc('%u')",
- finfo->dobj.catId.oid);
-
- res = ExecuteSqlQueryForSingleRow(fout, query->data);
-
- proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset"));
- if (PQgetisnull(res, 0, PQfnumber(res, "prosqlbody")))
+ proretset = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proretset"));
+ if (PQgetisnull(funcs, funcidx, PQfnumber(funcs, "prosqlbody")))
{
- prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc"));
- probin = PQgetvalue(res, 0, PQfnumber(res, "probin"));
+ prosrc = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prosrc"));
+ probin = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "probin"));
prosqlbody = NULL;
}
else
{
prosrc = NULL;
probin = NULL;
- prosqlbody = PQgetvalue(res, 0, PQfnumber(res, "prosqlbody"));
- }
- funcargs = PQgetvalue(res, 0, PQfnumber(res, "funcargs"));
- funciargs = PQgetvalue(res, 0, PQfnumber(res, "funciargs"));
- funcresult = PQgetvalue(res, 0, PQfnumber(res, "funcresult"));
- protrftypes = PQgetvalue(res, 0, PQfnumber(res, "protrftypes"));
- prokind = PQgetvalue(res, 0, PQfnumber(res, "prokind"));
- provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
- proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
- prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
- proleakproof = PQgetvalue(res, 0, PQfnumber(res, "proleakproof"));
- proconfig = PQgetvalue(res, 0, PQfnumber(res, "proconfig"));
- procost = PQgetvalue(res, 0, PQfnumber(res, "procost"));
- prorows = PQgetvalue(res, 0, PQfnumber(res, "prorows"));
- prosupport = PQgetvalue(res, 0, PQfnumber(res, "prosupport"));
- proparallel = PQgetvalue(res, 0, PQfnumber(res, "proparallel"));
- lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
+ prosqlbody = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prosqlbody"));
+ }
+ funcargs = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "funcargs"));
+ funciargs = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "funciargs"));
+ funcresult = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "funcresult"));
+ protrftypes = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "protrftypes"));
+ prokind = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prokind"));
+ provolatile = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "provolatile"));
+ proisstrict = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proisstrict"));
+ prosecdef = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prosecdef"));
+ proleakproof = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proleakproof"));
+ proconfig = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proconfig"));
+ procost = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "procost"));
+ prorows = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prorows"));
+ prosupport = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "prosupport"));
+ proparallel = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "proparallel"));
+ lanname = PQgetvalue(funcs, funcidx, PQfnumber(funcs, "lanname"));
/*
* See backend/commands/functioncmds.c for details of how the 'AS' clause
@@ -12469,9 +12504,6 @@ dumpFunc(Archive *fout, const FuncInfo *finfo)
finfo->dobj.namespace->dobj.name,
NULL, finfo->rolname, &finfo->dacl);
- PQclear(res);
-
- destroyPQExpBuffer(query);
destroyPQExpBuffer(q);
destroyPQExpBuffer(delqry);
destroyPQExpBuffer(asPart);
--
2.39.3 (Apple Git-146)
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: pg_dump: optimize dumpFunc()
In-Reply-To: <Zqu9aaYtNyHj8V7m@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox