Skip to content

Commit 0decd5e

Browse files
nmischnmisch
committed
Sort dump objects independent of OIDs, for the 7 holdout object types.
pg_dump sorts objects by their logical names, e.g. (nspname, relname, tgname), before dependency-driven reordering. That removes one source of logically-identical databases differing in their schema-only dumps. In other words, it helps with schema diffing. The logical name sort ignored essential sort keys for constraints, operators, PUBLICATION ... FOR TABLE, PUBLICATION ... FOR TABLES IN SCHEMA, operator classes, and operator families. pg_dump's sort then depended on object OID, yielding spurious schema diffs. After this change, OIDs affect dump order only in the event of catalog corruption. While pg_dump also wrongly ignored pg_collation.collencoding, CREATE COLLATION restrictions have been keeping that imperceptible in practical use. Use techniques like we use for object types already having full sort key coverage. Where the pertinent queries weren't fetching the ignored sort keys, this adds columns to those queries and stores those keys in memory for the long term. The ignorance of sort keys became more problematic when commit 172259a added a schema diff test sensitive to it. Buildfarm member hippopotamus witnessed that. However, dump order stability isn't a new goal, and this might avoid other dump comparison failures. Hence, back-patch to v13 (all supported versions). Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/20250707192654.9e.nmisch@google.com Backpatch-through: 13
1 parent 3357471 commit 0decd5e

File tree

6 files changed

+335
-30
lines changed

6 files changed

+335
-30
lines changed

‎src/bin/pg_dump/common.c

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@
1717

1818
#include <ctype.h>
1919

20+
#include "catalog/pg_am_d.h"
2021
#include "catalog/pg_class_d.h"
2122
#include "catalog/pg_collation_d.h"
2223
#include "catalog/pg_extension_d.h"
@@ -944,6 +945,24 @@ findOprByOid(Oid oid)
944945
return (OprInfo *) dobj;
945946
}
946947

948+
/*
949+
* findAccessMethodByOid
950+
* finds the DumpableObject for the access method with the given oid
951+
* returns NULL if not found
952+
*/
953+
AccessMethodInfo *
954+
findAccessMethodByOid(Oid oid)
955+
{
956+
CatalogId catId;
957+
DumpableObject *dobj;
958+
959+
catId.tableoid = AccessMethodRelationId;
960+
catId.oid = oid;
961+
dobj = findObjectByCatalogId(catId);
962+
Assert(dobj == NULL || dobj->objType == DO_ACCESS_METHOD);
963+
return (AccessMethodInfo *) dobj;
964+
}
965+
947966
/*
948967
* findCollationByOid
949968
* finds the DumpableObject for the collation with the given oid

‎src/bin/pg_dump/pg_dump.c

Lines changed: 48 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -2207,6 +2207,13 @@ selectDumpableProcLang(ProcLangInfo *plang, Archive *fout)
22072207
static void
22082208
selectDumpableAccessMethod(AccessMethodInfo *method, Archive *fout)
22092209
{
2210+
/* see getAccessMethods() comment about v9.6. */
2211+
if (fout->remoteVersion < 90600)
2212+
{
2213+
method->dobj.dump = DUMP_COMPONENT_NONE;
2214+
return;
2215+
}
2216+
22102217
if (checkExtensionMembership(&method->dobj, fout))
22112218
return; /* extension membership overrides all else */
22122219

@@ -6262,6 +6269,8 @@ getOperators(Archive *fout)
62626269
int i_oprnamespace;
62636270
int i_oprowner;
62646271
int i_oprkind;
6272+
int i_oprleft;
6273+
int i_oprright;
62656274
int i_oprcode;
62666275

62676276
/*
@@ -6273,6 +6282,8 @@ getOperators(Archive *fout)
62736282
"oprnamespace, "
62746283
"oprowner, "
62756284
"oprkind, "
6285+
"oprleft, "
6286+
"oprright, "
62766287
"oprcode::oid AS oprcode "
62776288
"FROM pg_operator");
62786289

@@ -6288,6 +6299,8 @@ getOperators(Archive *fout)
62886299
i_oprnamespace = PQfnumber(res, "oprnamespace");
62896300
i_oprowner = PQfnumber(res, "oprowner");
62906301
i_oprkind = PQfnumber(res, "oprkind");
6302+
i_oprleft = PQfnumber(res, "oprleft");
6303+
i_oprright = PQfnumber(res, "oprright");
62916304
i_oprcode = PQfnumber(res, "oprcode");
62926305

62936306
for (i = 0; i < ntups; i++)
@@ -6301,6 +6314,8 @@ getOperators(Archive *fout)
63016314
findNamespace(atooid(PQgetvalue(res, i, i_oprnamespace)));
63026315
oprinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_oprowner));
63036316
oprinfo[i].oprkind = (PQgetvalue(res, i, i_oprkind))[0];
6317+
oprinfo[i].oprleft = atooid(PQgetvalue(res, i, i_oprleft));
6318+
oprinfo[i].oprright = atooid(PQgetvalue(res, i, i_oprright));
63046319
oprinfo[i].oprcode = atooid(PQgetvalue(res, i, i_oprcode));
63056320

63066321
/* Decide whether we want to dump it */
@@ -6329,6 +6344,7 @@ getCollations(Archive *fout)
63296344
int i_collname;
63306345
int i_collnamespace;
63316346
int i_collowner;
6347+
int i_collencoding;
63326348

63336349
query = createPQExpBuffer();
63346350

@@ -6339,7 +6355,8 @@ getCollations(Archive *fout)
63396355

63406356
appendPQExpBufferStr(query, "SELECT tableoid, oid, collname, "
63416357
"collnamespace, "
6342-
"collowner "
6358+
"collowner, "
6359+
"collencoding "
63436360
"FROM pg_collation");
63446361

63456362
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -6353,6 +6370,7 @@ getCollations(Archive *fout)
63536370
i_collname = PQfnumber(res, "collname");
63546371
i_collnamespace = PQfnumber(res, "collnamespace");
63556372
i_collowner = PQfnumber(res, "collowner");
6373+
i_collencoding = PQfnumber(res, "collencoding");
63566374

63576375
for (i = 0; i < ntups; i++)
63586376
{
@@ -6364,6 +6382,7 @@ getCollations(Archive *fout)
63646382
collinfo[i].dobj.namespace =
63656383
findNamespace(atooid(PQgetvalue(res, i, i_collnamespace)));
63666384
collinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_collowner));
6385+
collinfo[i].collencoding = atoi(PQgetvalue(res, i, i_collencoding));
63676386

63686387
/* Decide whether we want to dump it */
63696388
selectDumpableObject(&(collinfo[i].dobj), fout);
@@ -6454,16 +6473,28 @@ getAccessMethods(Archive *fout)
64546473
int i_amhandler;
64556474
int i_amtype;
64566475

6457-
/* Before 9.6, there are no user-defined access methods */
6458-
if (fout->remoteVersion < 90600)
6459-
return;
6460-
64616476
query = createPQExpBuffer();
64626477

6463-
/* Select all access methods from pg_am table */
6464-
appendPQExpBufferStr(query, "SELECT tableoid, oid, amname, amtype, "
6465-
"amhandler::pg_catalog.regproc AS amhandler "
6466-
"FROM pg_am");
6478+
/*
6479+
* Select all access methods from pg_am table. v9.6 introduced CREATE
6480+
* ACCESS METHOD, so earlier versions usually have only built-in access
6481+
* methods. v9.6 also changed the access method API, replacing dozens of
6482+
* pg_am columns with amhandler. Even if a user created an access method
6483+
* by "INSERT INTO pg_am", we have no way to translate pre-v9.6 pg_am
6484+
* columns to a v9.6+ CREATE ACCESS METHOD. Hence, before v9.6, read
6485+
* pg_am just to facilitate findAccessMethodByOid() providing the
6486+
* OID-to-name mapping.
6487+
*/
6488+
appendPQExpBufferStr(query, "SELECT tableoid, oid, amname, ");
6489+
if (fout->remoteVersion >= 90600)
6490+
appendPQExpBufferStr(query,
6491+
"amtype, "
6492+
"amhandler::pg_catalog.regproc AS amhandler ");
6493+
else
6494+
appendPQExpBufferStr(query,
6495+
"'i'::pg_catalog.\"char\" AS amtype, "
6496+
"'-'::pg_catalog.regproc AS amhandler ");
6497+
appendPQExpBufferStr(query, "FROM pg_am");
64676498

64686499
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
64696500

@@ -6512,6 +6543,7 @@ getOpclasses(Archive *fout)
65126543
OpclassInfo *opcinfo;
65136544
int i_tableoid;
65146545
int i_oid;
6546+
int i_opcmethod;
65156547
int i_opcname;
65166548
int i_opcnamespace;
65176549
int i_opcowner;
@@ -6521,7 +6553,7 @@ getOpclasses(Archive *fout)
65216553
* system-defined opclasses at dump-out time.
65226554
*/
65236555

6524-
appendPQExpBufferStr(query, "SELECT tableoid, oid, opcname, "
6556+
appendPQExpBufferStr(query, "SELECT tableoid, oid, opcmethod, opcname, "
65256557
"opcnamespace, "
65266558
"opcowner "
65276559
"FROM pg_opclass");
@@ -6534,6 +6566,7 @@ getOpclasses(Archive *fout)
65346566

65356567
i_tableoid = PQfnumber(res, "tableoid");
65366568
i_oid = PQfnumber(res, "oid");
6569+
i_opcmethod = PQfnumber(res, "opcmethod");
65376570
i_opcname = PQfnumber(res, "opcname");
65386571
i_opcnamespace = PQfnumber(res, "opcnamespace");
65396572
i_opcowner = PQfnumber(res, "opcowner");
@@ -6547,6 +6580,7 @@ getOpclasses(Archive *fout)
65476580
opcinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_opcname));
65486581
opcinfo[i].dobj.namespace =
65496582
findNamespace(atooid(PQgetvalue(res, i, i_opcnamespace)));
6583+
opcinfo[i].opcmethod = atooid(PQgetvalue(res, i, i_opcmethod));
65506584
opcinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_opcowner));
65516585

65526586
/* Decide whether we want to dump it */
@@ -6572,6 +6606,7 @@ getOpfamilies(Archive *fout)
65726606
OpfamilyInfo *opfinfo;
65736607
int i_tableoid;
65746608
int i_oid;
6609+
int i_opfmethod;
65756610
int i_opfname;
65766611
int i_opfnamespace;
65776612
int i_opfowner;
@@ -6583,7 +6618,7 @@ getOpfamilies(Archive *fout)
65836618
* system-defined opfamilies at dump-out time.
65846619
*/
65856620

6586-
appendPQExpBufferStr(query, "SELECT tableoid, oid, opfname, "
6621+
appendPQExpBufferStr(query, "SELECT tableoid, oid, opfmethod, opfname, "
65876622
"opfnamespace, "
65886623
"opfowner "
65896624
"FROM pg_opfamily");
@@ -6597,6 +6632,7 @@ getOpfamilies(Archive *fout)
65976632
i_tableoid = PQfnumber(res, "tableoid");
65986633
i_oid = PQfnumber(res, "oid");
65996634
i_opfname = PQfnumber(res, "opfname");
6635+
i_opfmethod = PQfnumber(res, "opfmethod");
66006636
i_opfnamespace = PQfnumber(res, "opfnamespace");
66016637
i_opfowner = PQfnumber(res, "opfowner");
66026638

@@ -6609,6 +6645,7 @@ getOpfamilies(Archive *fout)
66096645
opfinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_opfname));
66106646
opfinfo[i].dobj.namespace =
66116647
findNamespace(atooid(PQgetvalue(res, i, i_opfnamespace)));
6648+
opfinfo[i].opfmethod = atooid(PQgetvalue(res, i, i_opfmethod));
66126649
opfinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_opfowner));
66136650

66146651
/* Decide whether we want to dump it */

‎src/bin/pg_dump/pg_dump.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -260,6 +260,8 @@ typedef struct _oprInfo
260260
DumpableObject dobj;
261261
const char *rolname;
262262
char oprkind;
263+
Oid oprleft;
264+
Oid oprright;
263265
Oid oprcode;
264266
} OprInfo;
265267

@@ -273,19 +275,22 @@ typedef struct _accessMethodInfo
273275
typedef struct _opclassInfo
274276
{
275277
DumpableObject dobj;
278+
Oid opcmethod;
276279
const char *rolname;
277280
} OpclassInfo;
278281

279282
typedef struct _opfamilyInfo
280283
{
281284
DumpableObject dobj;
285+
Oid opfmethod;
282286
const char *rolname;
283287
} OpfamilyInfo;
284288

285289
typedef struct _collInfo
286290
{
287291
DumpableObject dobj;
288292
const char *rolname;
293+
int collencoding;
289294
} CollInfo;
290295

291296
typedef struct _convInfo
@@ -760,6 +765,7 @@ extern TableInfo *findTableByOid(Oid oid);
760765
extern TypeInfo *findTypeByOid(Oid oid);
761766
extern FuncInfo *findFuncByOid(Oid oid);
762767
extern OprInfo *findOprByOid(Oid oid);
768+
extern AccessMethodInfo *findAccessMethodByOid(Oid oid);
763769
extern CollInfo *findCollationByOid(Oid oid);
764770
extern NamespaceInfo *findNamespaceByOid(Oid oid);
765771
extern ExtensionInfo *findExtensionByOid(Oid oid);

0 commit comments

Comments
 (0)