Skip to content
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Commit 904ce45

Browse files
committedAug 31, 2021
Cache the results of format_type() queries in pg_dump.
There's long been a "TODO: there might be some value in caching the results" annotation on pg_dump's getFormattedTypeName function; but we hadn't gotten around to checking what it was costing us to repetitively look up type names. It turns out that when dumping the current regression database, about 10% of the total number of queries issued are duplicative format_type() queries. However, Hubert Depesz Lubaczewski reported a not-unusual case where these account for over half of the queries issued by pg_dump. Individually these queries aren't expensive, but when network lag is a factor, they add up to a problem. We can very easily add some caching to getFormattedTypeName to solve it. Since this is such a simple fix and can have a visible performance benefit, back-patch to all supported branches. Discussion: https://postgr.es/m/20210826084430.GA26282@depesz.com
1 parent c8213aa commit 904ce45

File tree

2 files changed

+15
-4
lines changed

2 files changed

+15
-4
lines changed
 

‎src/bin/pg_dump/pg_dump.c

Lines changed: 11 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5067,6 +5067,7 @@ getTypes(Archive *fout, int *numTypes)
50675067
tyinfo[i].dobj.namespace =
50685068
findNamespace(fout,
50695069
atooid(PQgetvalue(res, i, i_typnamespace)));
5070+
tyinfo[i].ftypname = NULL; /* may get filled later */
50705071
tyinfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_rolname));
50715072
tyinfo[i].typacl = pg_strdup(PQgetvalue(res, i, i_typacl));
50725073
tyinfo[i].rtypacl = pg_strdup(PQgetvalue(res, i, i_rtypacl));
@@ -18735,12 +18736,11 @@ findDumpableDependencies(ArchiveHandle *AH, DumpableObject *dobj,
1873518736
*
1873618737
* This does not guarantee to schema-qualify the output, so it should not
1873718738
* be used to create the target object name for CREATE or ALTER commands.
18738-
*
18739-
* TODO: there might be some value in caching the results.
1874018739
*/
1874118740
static char *
1874218741
getFormattedTypeName(Archive *fout, Oid oid, OidOptions opts)
1874318742
{
18743+
TypeInfo *typeInfo;
1874418744
char *result;
1874518745
PQExpBuffer query;
1874618746
PGresult *res;
@@ -18753,6 +18753,11 @@ getFormattedTypeName(Archive *fout, Oid oid, OidOptions opts)
1875318753
return pg_strdup("NONE");
1875418754
}
1875518755

18756+
/* see if we have the result cached in the type's TypeInfo record */
18757+
typeInfo = findTypeByOid(oid);
18758+
if (typeInfo && typeInfo->ftypname)
18759+
return pg_strdup(typeInfo->ftypname);
18760+
1875618761
query = createPQExpBuffer();
1875718762
appendPQExpBuffer(query, "SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
1875818763
oid);
@@ -18765,6 +18770,10 @@ getFormattedTypeName(Archive *fout, Oid oid, OidOptions opts)
1876518770
PQclear(res);
1876618771
destroyPQExpBuffer(query);
1876718772

18773+
/* cache a copy for later requests */
18774+
if (typeInfo)
18775+
typeInfo->ftypname = pg_strdup(result);
18776+
1876818777
return result;
1876918778
}
1877018779

‎src/bin/pg_dump/pg_dump.h

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -163,9 +163,11 @@ typedef struct _typeInfo
163163
DumpableObject dobj;
164164

165165
/*
166-
* Note: dobj.name is the pg_type.typname entry. format_type() might
167-
* produce something different than typname
166+
* Note: dobj.name is the raw pg_type.typname entry. ftypname is the
167+
* result of format_type(), which will be quoted if needed, and might be
168+
* schema-qualified too.
168169
*/
170+
char *ftypname;
169171
char *rolname; /* name of owner, or empty string */
170172
char *typacl;
171173
char *rtypacl;

0 commit comments

Comments
 (0)
Failed to load comments.