Skip to content

Commit 0ac1581

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 9affed2 commit 0ac1581

File tree

6 files changed

+335
-33
lines changed

6 files changed

+335
-33
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"
@@ -902,6 +903,24 @@ findOprByOid(Oid oid)
902903
return (OprInfo *) dobj;
903904
}
904905

906+
/*
907+
* findAccessMethodByOid
908+
* finds the DumpableObject for the access method with the given oid
909+
* returns NULL if not found
910+
*/
911+
AccessMethodInfo *
912+
findAccessMethodByOid(Oid oid)
913+
{
914+
CatalogId catId;
915+
DumpableObject *dobj;
916+
917+
catId.tableoid = AccessMethodRelationId;
918+
catId.oid = oid;
919+
dobj = findObjectByCatalogId(catId);
920+
Assert(dobj == NULL || dobj->objType == DO_ACCESS_METHOD);
921+
return (AccessMethodInfo *) dobj;
922+
}
923+
905924
/*
906925
* findCollationByOid
907926
* finds the DumpableObject for the collation with the given oid

‎src/bin/pg_dump/pg_dump.c

Lines changed: 48 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1970,6 +1970,13 @@ selectDumpableProcLang(ProcLangInfo *plang, Archive *fout)
19701970
static void
19711971
selectDumpableAccessMethod(AccessMethodInfo *method, Archive *fout)
19721972
{
1973+
/* see getAccessMethods() comment about v9.6. */
1974+
if (fout->remoteVersion < 90600)
1975+
{
1976+
method->dobj.dump = DUMP_COMPONENT_NONE;
1977+
return;
1978+
}
1979+
19731980
if (checkExtensionMembership(&method->dobj, fout))
19741981
return; /* extension membership overrides all else */
19751982

@@ -5643,6 +5650,8 @@ getOperators(Archive *fout, int *numOprs)
56435650
int i_oprnamespace;
56445651
int i_oprowner;
56455652
int i_oprkind;
5653+
int i_oprleft;
5654+
int i_oprright;
56465655
int i_oprcode;
56475656

56485657
/*
@@ -5654,6 +5663,8 @@ getOperators(Archive *fout, int *numOprs)
56545663
"oprnamespace, "
56555664
"oprowner, "
56565665
"oprkind, "
5666+
"oprleft, "
5667+
"oprright, "
56575668
"oprcode::oid AS oprcode "
56585669
"FROM pg_operator");
56595670

@@ -5670,6 +5681,8 @@ getOperators(Archive *fout, int *numOprs)
56705681
i_oprnamespace = PQfnumber(res, "oprnamespace");
56715682
i_oprowner = PQfnumber(res, "oprowner");
56725683
i_oprkind = PQfnumber(res, "oprkind");
5684+
i_oprleft = PQfnumber(res, "oprleft");
5685+
i_oprright = PQfnumber(res, "oprright");
56735686
i_oprcode = PQfnumber(res, "oprcode");
56745687

56755688
for (i = 0; i < ntups; i++)
@@ -5683,6 +5696,8 @@ getOperators(Archive *fout, int *numOprs)
56835696
findNamespace(atooid(PQgetvalue(res, i, i_oprnamespace)));
56845697
oprinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_oprowner));
56855698
oprinfo[i].oprkind = (PQgetvalue(res, i, i_oprkind))[0];
5699+
oprinfo[i].oprleft = atooid(PQgetvalue(res, i, i_oprleft));
5700+
oprinfo[i].oprright = atooid(PQgetvalue(res, i, i_oprright));
56865701
oprinfo[i].oprcode = atooid(PQgetvalue(res, i, i_oprcode));
56875702

56885703
/* Decide whether we want to dump it */
@@ -5716,6 +5731,7 @@ getCollations(Archive *fout, int *numCollations)
57165731
int i_collname;
57175732
int i_collnamespace;
57185733
int i_collowner;
5734+
int i_collencoding;
57195735

57205736
query = createPQExpBuffer();
57215737

@@ -5726,7 +5742,8 @@ getCollations(Archive *fout, int *numCollations)
57265742

57275743
appendPQExpBufferStr(query, "SELECT tableoid, oid, collname, "
57285744
"collnamespace, "
5729-
"collowner "
5745+
"collowner, "
5746+
"collencoding "
57305747
"FROM pg_collation");
57315748

57325749
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -5741,6 +5758,7 @@ getCollations(Archive *fout, int *numCollations)
57415758
i_collname = PQfnumber(res, "collname");
57425759
i_collnamespace = PQfnumber(res, "collnamespace");
57435760
i_collowner = PQfnumber(res, "collowner");
5761+
i_collencoding = PQfnumber(res, "collencoding");
57445762

57455763
for (i = 0; i < ntups; i++)
57465764
{
@@ -5752,6 +5770,7 @@ getCollations(Archive *fout, int *numCollations)
57525770
collinfo[i].dobj.namespace =
57535771
findNamespace(atooid(PQgetvalue(res, i, i_collnamespace)));
57545772
collinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_collowner));
5773+
collinfo[i].collencoding = atoi(PQgetvalue(res, i, i_collencoding));
57555774

57565775
/* Decide whether we want to dump it */
57575776
selectDumpableObject(&(collinfo[i].dobj), fout);
@@ -5853,19 +5872,28 @@ getAccessMethods(Archive *fout, int *numAccessMethods)
58535872
int i_amhandler;
58545873
int i_amtype;
58555874

5856-
/* Before 9.6, there are no user-defined access methods */
5857-
if (fout->remoteVersion < 90600)
5858-
{
5859-
*numAccessMethods = 0;
5860-
return NULL;
5861-
}
5862-
58635875
query = createPQExpBuffer();
58645876

5865-
/* Select all access methods from pg_am table */
5866-
appendPQExpBufferStr(query, "SELECT tableoid, oid, amname, amtype, "
5867-
"amhandler::pg_catalog.regproc AS amhandler "
5868-
"FROM pg_am");
5877+
/*
5878+
* Select all access methods from pg_am table. v9.6 introduced CREATE
5879+
* ACCESS METHOD, so earlier versions usually have only built-in access
5880+
* methods. v9.6 also changed the access method API, replacing dozens of
5881+
* pg_am columns with amhandler. Even if a user created an access method
5882+
* by "INSERT INTO pg_am", we have no way to translate pre-v9.6 pg_am
5883+
* columns to a v9.6+ CREATE ACCESS METHOD. Hence, before v9.6, read
5884+
* pg_am just to facilitate findAccessMethodByOid() providing the
5885+
* OID-to-name mapping.
5886+
*/
5887+
appendPQExpBufferStr(query, "SELECT tableoid, oid, amname, ");
5888+
if (fout->remoteVersion >= 90600)
5889+
appendPQExpBufferStr(query,
5890+
"amtype, "
5891+
"amhandler::pg_catalog.regproc AS amhandler ");
5892+
else
5893+
appendPQExpBufferStr(query,
5894+
"'i'::pg_catalog.\"char\" AS amtype, "
5895+
"'-'::pg_catalog.regproc AS amhandler ");
5896+
appendPQExpBufferStr(query, "FROM pg_am");
58695897

58705898
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
58715899

@@ -5920,6 +5948,7 @@ getOpclasses(Archive *fout, int *numOpclasses)
59205948
OpclassInfo *opcinfo;
59215949
int i_tableoid;
59225950
int i_oid;
5951+
int i_opcmethod;
59235952
int i_opcname;
59245953
int i_opcnamespace;
59255954
int i_opcowner;
@@ -5929,7 +5958,7 @@ getOpclasses(Archive *fout, int *numOpclasses)
59295958
* system-defined opclasses at dump-out time.
59305959
*/
59315960

5932-
appendPQExpBufferStr(query, "SELECT tableoid, oid, opcname, "
5961+
appendPQExpBufferStr(query, "SELECT tableoid, oid, opcmethod, opcname, "
59335962
"opcnamespace, "
59345963
"opcowner "
59355964
"FROM pg_opclass");
@@ -5943,6 +5972,7 @@ getOpclasses(Archive *fout, int *numOpclasses)
59435972

59445973
i_tableoid = PQfnumber(res, "tableoid");
59455974
i_oid = PQfnumber(res, "oid");
5975+
i_opcmethod = PQfnumber(res, "opcmethod");
59465976
i_opcname = PQfnumber(res, "opcname");
59475977
i_opcnamespace = PQfnumber(res, "opcnamespace");
59485978
i_opcowner = PQfnumber(res, "opcowner");
@@ -5956,6 +5986,7 @@ getOpclasses(Archive *fout, int *numOpclasses)
59565986
opcinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_opcname));
59575987
opcinfo[i].dobj.namespace =
59585988
findNamespace(atooid(PQgetvalue(res, i, i_opcnamespace)));
5989+
opcinfo[i].opcmethod = atooid(PQgetvalue(res, i, i_opcmethod));
59595990
opcinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_opcowner));
59605991

59615992
/* Decide whether we want to dump it */
@@ -5986,6 +6017,7 @@ getOpfamilies(Archive *fout, int *numOpfamilies)
59866017
OpfamilyInfo *opfinfo;
59876018
int i_tableoid;
59886019
int i_oid;
6020+
int i_opfmethod;
59896021
int i_opfname;
59906022
int i_opfnamespace;
59916023
int i_opfowner;
@@ -5997,7 +6029,7 @@ getOpfamilies(Archive *fout, int *numOpfamilies)
59976029
* system-defined opfamilies at dump-out time.
59986030
*/
59996031

6000-
appendPQExpBufferStr(query, "SELECT tableoid, oid, opfname, "
6032+
appendPQExpBufferStr(query, "SELECT tableoid, oid, opfmethod, opfname, "
60016033
"opfnamespace, "
60026034
"opfowner "
60036035
"FROM pg_opfamily");
@@ -6012,6 +6044,7 @@ getOpfamilies(Archive *fout, int *numOpfamilies)
60126044
i_tableoid = PQfnumber(res, "tableoid");
60136045
i_oid = PQfnumber(res, "oid");
60146046
i_opfname = PQfnumber(res, "opfname");
6047+
i_opfmethod = PQfnumber(res, "opfmethod");
60156048
i_opfnamespace = PQfnumber(res, "opfnamespace");
60166049
i_opfowner = PQfnumber(res, "opfowner");
60176050

@@ -6024,6 +6057,7 @@ getOpfamilies(Archive *fout, int *numOpfamilies)
60246057
opfinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_opfname));
60256058
opfinfo[i].dobj.namespace =
60266059
findNamespace(atooid(PQgetvalue(res, i, i_opfnamespace)));
6060+
opfinfo[i].opfmethod = atooid(PQgetvalue(res, i, i_opfmethod));
60276061
opfinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_opfowner));
60286062

60296063
/* 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
@@ -250,6 +250,8 @@ typedef struct _oprInfo
250250
DumpableObject dobj;
251251
const char *rolname;
252252
char oprkind;
253+
Oid oprleft;
254+
Oid oprright;
253255
Oid oprcode;
254256
} OprInfo;
255257

@@ -263,19 +265,22 @@ typedef struct _accessMethodInfo
263265
typedef struct _opclassInfo
264266
{
265267
DumpableObject dobj;
268+
Oid opcmethod;
266269
const char *rolname;
267270
} OpclassInfo;
268271

269272
typedef struct _opfamilyInfo
270273
{
271274
DumpableObject dobj;
275+
Oid opfmethod;
272276
const char *rolname;
273277
} OpfamilyInfo;
274278

275279
typedef struct _collInfo
276280
{
277281
DumpableObject dobj;
278282
const char *rolname;
283+
int collencoding;
279284
} CollInfo;
280285

281286
typedef struct _convInfo
@@ -697,6 +702,7 @@ extern TableInfo *findTableByOid(Oid oid);
697702
extern TypeInfo *findTypeByOid(Oid oid);
698703
extern FuncInfo *findFuncByOid(Oid oid);
699704
extern OprInfo *findOprByOid(Oid oid);
705+
extern AccessMethodInfo *findAccessMethodByOid(Oid oid);
700706
extern CollInfo *findCollationByOid(Oid oid);
701707
extern NamespaceInfo *findNamespaceByOid(Oid oid);
702708
extern ExtensionInfo *findExtensionByOid(Oid oid);

0 commit comments

Comments
 (0)