Bruce Momjian wrote: > Bruce Momjian wrote: > > OK, thanks to RhodiumToad on IRC, I was able to determine the cause of > > the two reported pg_upgrade problems he saw via IRC. It seems toast > > tables have xids and pg_dump is not preserving the toast relfrozenxids > > as it should. Heap tables have preserved relfrozenxids, but if you > > update a heap row but don't change the toast value, and the old heap row > > is later removed, the toast table can have an older relfrozenxids than > > the heap table. > > > > The fix for this is to have pg_dump preserve toast relfrozenxids, which > > can be easily added and backpatched. We might want to push a 9.0.4 for > > this. Second, we need to find a way for people to detect and fix > > existing systems that have this problem, perhaps looming when the > > pg_class relfrozenxid passes the toast relfrozenxid, and thirdly, we > > need to figure out how to get this information to users. Perhaps the > > communication comes through the 9.0.4 release announcement. > > I am not sure how to interpret the lack of replies to this email. > Either it is confidence, shock, or we told you so. ;-) > > Anyway, the attached patch fixes the problem. The fix is for pg_dump's > binary upgrade mode. This would need to be backpatched back to 8.4 > because pg_migrator needs this too.
OK, I have applied the attached three patches to 8.4, 9.0, and 9.1. They are all slightly different because of code drift, and I created a unified diff which I find is clearer for single-line changes. I was very careful about the patching of queries because many of these queries are only activated when dumping an older database, and are therefore hard to test for SQL query errors. I included all the version patches in case someone sees something I missed. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 3842895..c5057f7 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -3185,6 +3185,8 @@ getTables(int *numTables) int i_relhasrules; int i_relhasoids; int i_relfrozenxid; + int i_toastoid; + int i_toastfrozenxid; int i_owning_tab; int i_owning_col; int i_reltablespace; @@ -3226,7 +3228,8 @@ getTables(int *numTables) "(%s c.relowner) AS rolname, " "c.relchecks, c.relhastriggers, " "c.relhasindex, c.relhasrules, c.relhasoids, " - "c.relfrozenxid, " + "c.relfrozenxid, tc.oid AS toid, " + "tc.relfrozenxid AS tfrozenxid, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, " @@ -3259,6 +3262,8 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, " @@ -3290,6 +3295,8 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, " @@ -3321,6 +3328,8 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " "NULL AS reltablespace, " @@ -3348,6 +3357,8 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " "NULL AS reltablespace, " @@ -3370,6 +3381,8 @@ getTables(int *numTables) "relhasindex, relhasrules, " "'t'::bool AS relhasoids, " "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " "NULL AS reltablespace, " @@ -3446,6 +3459,8 @@ getTables(int *numTables) i_relhasrules = PQfnumber(res, "relhasrules"); i_relhasoids = PQfnumber(res, "relhasoids"); i_relfrozenxid = PQfnumber(res, "relfrozenxid"); + i_toastoid = PQfnumber(res, "toid"); + i_toastfrozenxid = PQfnumber(res, "tfrozenxid"); i_owning_tab = PQfnumber(res, "owning_tab"); i_owning_col = PQfnumber(res, "owning_col"); i_reltablespace = PQfnumber(res, "reltablespace"); @@ -3484,6 +3499,8 @@ getTables(int *numTables) tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0); tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0); tblinfo[i].frozenxid = atooid(PQgetvalue(res, i, i_relfrozenxid)); + tblinfo[i].toast_oid = atooid(PQgetvalue(res, i, i_toastoid)); + tblinfo[i].toast_frozenxid = atooid(PQgetvalue(res, i, i_toastfrozenxid)); tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks)); if (PQgetisnull(res, i, i_owning_tab)) { @@ -10044,13 +10061,23 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } } - appendPQExpBuffer(q, "\n-- For binary upgrade, set relfrozenxid.\n"); + appendPQExpBuffer(q, "\n-- For binary upgrade, set heap's relfrozenxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u'\n" "WHERE oid = ", tbinfo->frozenxid); appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout); appendPQExpBuffer(q, "::pg_catalog.regclass;\n"); + + if (tbinfo->toast_oid) + { + /* We preserve the toast oids, so we can use it during restore */ + appendPQExpBuffer(q, "\n-- For binary upgrade, set toast's relfrozenxid\n"); + appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" + "SET relfrozenxid = '%u'\n" + "WHERE oid = '%u';\n", + tbinfo->toast_frozenxid, tbinfo->toast_oid); + } } /* Loop dumping statistics and storage statements */ diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 3339bf7..390b20c 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -227,6 +227,8 @@ typedef struct _tableInfo bool hastriggers; /* does it have any triggers? */ bool hasoids; /* does it have OIDs? */ uint32 frozenxid; /* for restore frozen xid */ + Oid toast_oid; /* for restore toast frozen xid */ + uint32 toast_frozenxid;/* for restore toast frozen xid */ int ncheck; /* # of CHECK expressions */ /* these two are set only if table is a sequence owned by a column: */ Oid owning_tab; /* OID of table owning sequence */
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index f93affd..8721e65 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -3409,6 +3409,8 @@ getTables(int *numTables) int i_relhasrules; int i_relhasoids; int i_relfrozenxid; + int i_toastoid; + int i_toastfrozenxid; int i_owning_tab; int i_owning_col; int i_reltablespace; @@ -3451,7 +3453,8 @@ getTables(int *numTables) "(%s c.relowner) AS rolname, " "c.relchecks, c.relhastriggers, " "c.relhasindex, c.relhasrules, c.relhasoids, " - "c.relfrozenxid, " + "c.relfrozenxid, tc.oid AS toid, " + "tc.relfrozenxid AS tfrozenxid, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -3484,7 +3487,8 @@ getTables(int *numTables) "(%s c.relowner) AS rolname, " "c.relchecks, c.relhastriggers, " "c.relhasindex, c.relhasrules, c.relhasoids, " - "c.relfrozenxid, " + "c.relfrozenxid, tc.oid AS toid, " + "tc.relfrozenxid AS tfrozenxid, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -3518,6 +3522,8 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -3550,6 +3556,8 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -3582,6 +3590,8 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -3610,6 +3620,8 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -3633,6 +3645,8 @@ getTables(int *numTables) "relhasindex, relhasrules, " "'t'::bool AS relhasoids, " "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -3666,6 +3680,8 @@ getTables(int *numTables) "relhasindex, relhasrules, " "'t'::bool AS relhasoids, " "0 as relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -3711,6 +3727,8 @@ getTables(int *numTables) i_relhasrules = PQfnumber(res, "relhasrules"); i_relhasoids = PQfnumber(res, "relhasoids"); i_relfrozenxid = PQfnumber(res, "relfrozenxid"); + i_toastoid = PQfnumber(res, "toid"); + i_toastfrozenxid = PQfnumber(res, "tfrozenxid"); i_owning_tab = PQfnumber(res, "owning_tab"); i_owning_col = PQfnumber(res, "owning_col"); i_reltablespace = PQfnumber(res, "reltablespace"); @@ -3750,6 +3768,8 @@ getTables(int *numTables) tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0); tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0); tblinfo[i].frozenxid = atooid(PQgetvalue(res, i, i_relfrozenxid)); + tblinfo[i].toast_oid = atooid(PQgetvalue(res, i, i_toastoid)); + tblinfo[i].toast_frozenxid = atooid(PQgetvalue(res, i, i_toastfrozenxid)); if (PQgetisnull(res, i, i_reloftype)) tblinfo[i].reloftype = NULL; else @@ -10852,13 +10872,23 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } } - appendPQExpBuffer(q, "\n-- For binary upgrade, set relfrozenxid.\n"); + appendPQExpBuffer(q, "\n-- For binary upgrade, set heap's relfrozenxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u'\n" "WHERE oid = ", tbinfo->frozenxid); appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout); appendPQExpBuffer(q, "::pg_catalog.regclass;\n"); + + if (tbinfo->toast_oid) + { + /* We preserve the toast oids, so we can use it during restore */ + appendPQExpBuffer(q, "\n-- For binary upgrade, set toast's relfrozenxid\n"); + appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" + "SET relfrozenxid = '%u'\n" + "WHERE oid = '%u';\n", + tbinfo->toast_frozenxid, tbinfo->toast_oid); + } } /* Loop dumping statistics and storage statements */ diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index c309f69..1dc7157 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -228,6 +228,8 @@ typedef struct _tableInfo bool hastriggers; /* does it have any triggers? */ bool hasoids; /* does it have OIDs? */ uint32 frozenxid; /* for restore frozen xid */ + Oid toast_oid; /* for restore toast frozen xid */ + uint32 toast_frozenxid;/* for restore toast frozen xid */ int ncheck; /* # of CHECK expressions */ char *reloftype; /* underlying type for typed table */ /* these two are set only if table is a sequence owned by a column: */
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 3f6e77b..1ccdb4d 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -3812,6 +3812,8 @@ getTables(int *numTables) int i_relhasrules; int i_relhasoids; int i_relfrozenxid; + int i_toastoid; + int i_toastfrozenxid; int i_relpersistence; int i_owning_tab; int i_owning_col; @@ -3855,7 +3857,9 @@ getTables(int *numTables) "(%s c.relowner) AS rolname, " "c.relchecks, c.relhastriggers, " "c.relhasindex, c.relhasrules, c.relhasoids, " - "c.relfrozenxid, c.relpersistence, " + "c.relfrozenxid, tc.oid AS toid, " + "tc.relfrozenxid AS tfrozenxid, " + "c.relpersistence, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -3889,7 +3893,9 @@ getTables(int *numTables) "(%s c.relowner) AS rolname, " "c.relchecks, c.relhastriggers, " "c.relhasindex, c.relhasrules, c.relhasoids, " - "c.relfrozenxid, 'p' AS relpersistence, " + "c.relfrozenxid, tc.oid AS toid, " + "tc.relfrozenxid AS tfrozenxid, " + "'p' AS relpersistence, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -3922,7 +3928,9 @@ getTables(int *numTables) "(%s c.relowner) AS rolname, " "c.relchecks, c.relhastriggers, " "c.relhasindex, c.relhasrules, c.relhasoids, " - "c.relfrozenxid, 'p' AS relpersistence, " + "c.relfrozenxid, tc.oid AS toid, " + "tc.relfrozenxid AS tfrozenxid, " + "'p' AS relpersistence, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -3955,7 +3963,10 @@ getTables(int *numTables) "(%s relowner) AS rolname, " "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " - "relfrozenxid, 'p' AS relpersistence, " + "relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " + "'p' AS relpersistence, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -3987,7 +3998,10 @@ getTables(int *numTables) "(%s relowner) AS rolname, " "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " - "0 AS relfrozenxid, 'p' AS relpersistence, " + "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " + "'p' AS relpersistence, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4019,7 +4033,10 @@ getTables(int *numTables) "(%s relowner) AS rolname, " "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " - "0 AS relfrozenxid, 'p' AS relpersistence, " + "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " + "'p' AS relpersistence, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4047,7 +4064,10 @@ getTables(int *numTables) "(%s relowner) AS rolname, " "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " - "0 AS relfrozenxid, 'p' AS relpersistence, " + "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " + "'p' AS relpersistence, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -4070,7 +4090,10 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, " "'t'::bool AS relhasoids, " - "0 AS relfrozenxid, 'p' AS relpersistence, " + "0 AS relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " + "'p' AS relpersistence, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -4103,7 +4126,10 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, " "'t'::bool AS relhasoids, " - "0 as relfrozenxid, 'p' AS relpersistence, " + "0 as relfrozenxid, " + "0 AS toid, " + "0 AS tfrozenxid, " + "'p' AS relpersistence, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -4149,6 +4175,8 @@ getTables(int *numTables) i_relhasrules = PQfnumber(res, "relhasrules"); i_relhasoids = PQfnumber(res, "relhasoids"); i_relfrozenxid = PQfnumber(res, "relfrozenxid"); + i_toastoid = PQfnumber(res, "toid"); + i_toastfrozenxid = PQfnumber(res, "tfrozenxid"); i_relpersistence = PQfnumber(res, "relpersistence"); i_owning_tab = PQfnumber(res, "owning_tab"); i_owning_col = PQfnumber(res, "owning_col"); @@ -4190,6 +4218,8 @@ getTables(int *numTables) tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0); tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0); tblinfo[i].frozenxid = atooid(PQgetvalue(res, i, i_relfrozenxid)); + tblinfo[i].toast_oid = atooid(PQgetvalue(res, i, i_toastoid)); + tblinfo[i].toast_frozenxid = atooid(PQgetvalue(res, i, i_toastfrozenxid)); if (PQgetisnull(res, i, i_reloftype)) tblinfo[i].reloftype = NULL; else @@ -12221,13 +12251,23 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } } - appendPQExpBuffer(q, "\n-- For binary upgrade, set relfrozenxid\n"); + appendPQExpBuffer(q, "\n-- For binary upgrade, set heap's relfrozenxid\n"); appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" "SET relfrozenxid = '%u'\n" "WHERE oid = ", tbinfo->frozenxid); appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout); appendPQExpBuffer(q, "::pg_catalog.regclass;\n"); + + if (tbinfo->toast_oid) + { + /* We preserve the toast oids, so we can use it during restore */ + appendPQExpBuffer(q, "\n-- For binary upgrade, set toast's relfrozenxid\n"); + appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n" + "SET relfrozenxid = '%u'\n" + "WHERE oid = '%u';\n", + tbinfo->toast_frozenxid, tbinfo->toast_oid); + } } /* Loop dumping statistics and storage statements */ diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 113ecb1..6559e23 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -248,6 +248,8 @@ typedef struct _tableInfo bool hastriggers; /* does it have any triggers? */ bool hasoids; /* does it have OIDs? */ uint32 frozenxid; /* for restore frozen xid */ + Oid toast_oid; /* for restore toast frozen xid */ + uint32 toast_frozenxid;/* for restore toast frozen xid */ int ncheck; /* # of CHECK expressions */ char *reloftype; /* underlying type for typed table */ /* these two are set only if table is a sequence owned by a column: */
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers