Re: [GENERAL] pg_upgrade problem

hubert depesz lubaczewski wrote:
> On Wed, Aug 31, 2011 at 01:23:05PM -0400, Bruce Momjian wrote:
> > Can you get me the 9.0.X pg_class.relfrozenxid for the toast and heap
> > tables involved?
>
> Sure:
>
> =# select oid::regclass, relfrozenxid from pg_class  where relname in ('transactions', 'pg_toast_106668498');
>              oid             | relfrozenxid
> -----------------------------+--------------
>  pg_toast.pg_toast_106668498 |   3673553926
>  transactions                |   3623560321
> (2 rows)

Working with depesz, I have found the cause.  The code I added to fix
pg_upgrade in 9.0.4 and earlier releases didn't handle old 8.3 servers
properly.  I mistakenly processed toast table with the same pg_dump
query as used for pre-8.4 toast tables, not realizing those were not
functional because there were no reloptions for toast tables in pre-8.4.

The attached applied patches fix all releases.  This will have to be
mentioned in the 9.0.5 release notes, and we should probably do the same
kind of announcement we did when I fixed this for 9.0.4.  :-(

Yeah, I should not have caused this bug.  It did not show up in any of
my testing.

--
  Bruce Momjian  <bruce@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
new file mode 100644
index b00e19b..c5816ae
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getTables(int *numTables)
*** 3256,3269 ****
           * owning column, if any (note this dependency is AUTO as of 8.2)
           */
          appendPQExpBuffer(query,
!                           "SELECT c.tableoid, c.oid, relname, "
!                           "relacl, relkind, relnamespace, "
!                           "(%s relowner) AS rolname, "
!                           "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, "
--- 3256,3268 ----
           * owning column, if any (note this dependency is AUTO as of 8.2)
           */
          appendPQExpBuffer(query,
!                           "SELECT c.tableoid, c.oid, c.relname, "
!                           "c.relacl, c.relkind, c.relnamespace, "
!                           "(%s c.relowner) AS rolname, "
!                           "c.relchecks, (c.reltriggers <> 0) AS relhastriggers, "
!                           "c.relhasindex, c.relhasrules, c.relhasoids, "
!                           "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, "
*************** getTables(int *numTables)
*** 3275,3281 ****
                            "d.classid = c.tableoid AND d.objid = c.oid AND "
                            "d.objsubid = 0 AND "
                            "d.refclassid = c.tableoid AND d.deptype = 'a') "
!                           "WHERE relkind in ('%c', '%c', '%c', '%c') "
                            "ORDER BY c.oid",
                            username_subquery,
                            RELKIND_SEQUENCE,
--- 3274,3281 ----
                            "d.classid = c.tableoid AND d.objid = c.oid AND "
                            "d.objsubid = 0 AND "
                            "d.refclassid = c.tableoid AND d.deptype = 'a') "
!                        "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) "
!                           "WHERE c.relkind in ('%c', '%c', '%c', '%c') "
                            "ORDER BY c.oid",
                            username_subquery,
                            RELKIND_SEQUENCE,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
new file mode 100644
index d6a547f..b73392b
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getTables(int *numTables)
*** 3516,3529 ****
           * owning column, if any (note this dependency is AUTO as of 8.2)
           */
          appendPQExpBuffer(query,
!                           "SELECT c.tableoid, c.oid, relname, "
!                           "relacl, relkind, relnamespace, "
!                           "(%s relowner) AS rolname, "
!                           "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, "
--- 3516,3528 ----
           * owning column, if any (note this dependency is AUTO as of 8.2)
           */
          appendPQExpBuffer(query,
!                           "SELECT c.tableoid, c.oid, c.relname, "
!                           "c.relacl, c.relkind, c.relnamespace, "
!                           "(%s c.relowner) AS rolname, "
!                           "c.relchecks, (c.reltriggers <> 0) AS relhastriggers, "
!                           "c.relhasindex, c.relhasrules, c.relhasoids, "
!                           "c.relfrozenxid, tc.oid AS toid, "
!                           "tc.relfrozenxid AS tfrozenxid, "
                            "NULL AS reloftype, "
                            "d.refobjid AS owning_tab, "
                            "d.refobjsubid AS owning_col, "
*************** getTables(int *numTables)
*** 3536,3542 ****
                            "d.classid = c.tableoid AND d.objid = c.oid AND "
                            "d.objsubid = 0 AND "
                            "d.refclassid = c.tableoid AND d.deptype = 'a') "
!                           "WHERE relkind in ('%c', '%c', '%c', '%c') "
                            "ORDER BY c.oid",
                            username_subquery,
                            RELKIND_SEQUENCE,
--- 3535,3542 ----
                            "d.classid = c.tableoid AND d.objid = c.oid AND "
                            "d.objsubid = 0 AND "
                            "d.refclassid = c.tableoid AND d.deptype = 'a') "
!                        "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) "
!                           "WHERE c.relkind in ('%c', '%c', '%c', '%c') "
                            "ORDER BY c.oid",
                            username_subquery,
                            RELKIND_SEQUENCE,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
new file mode 100644
index d7a147e..56db6e5
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getTables(int *numTables)
*** 3978,3991 ****
           * owning column, if any (note this dependency is AUTO as of 8.2)
           */
          appendPQExpBuffer(query,
!                           "SELECT c.tableoid, c.oid, relname, "
!                           "relacl, relkind, relnamespace, "
!                           "(%s relowner) AS rolname, "
!                           "relchecks, (reltriggers <> 0) AS relhastriggers, "
!                           "relhasindex, relhasrules, relhasoids, "
!                           "relfrozenxid, "
!                           "0 AS toid, "
!                           "0 AS tfrozenxid, "
                            "'p' AS relpersistence, "
                            "NULL AS reloftype, "
                            "d.refobjid AS owning_tab, "
--- 3978,3990 ----
           * owning column, if any (note this dependency is AUTO as of 8.2)
           */
          appendPQExpBuffer(query,
!                           "SELECT c.tableoid, c.oid, c.relname, "
!                           "c.relacl, c.relkind, c.relnamespace, "
!                           "(%s c.relowner) AS rolname, "
!                           "c.relchecks, (c.reltriggers <> 0) AS relhastriggers, "
!                           "c.relhasindex, c.relhasrules, c.relhasoids, "
!                           "c.relfrozenxid, tc.oid AS toid, "
!                           "tc.relfrozenxid AS tfrozenxid, "
                            "'p' AS relpersistence, "
                            "NULL AS reloftype, "
                            "d.refobjid AS owning_tab, "
*************** getTables(int *numTables)
*** 3999,4005 ****
                            "d.classid = c.tableoid AND d.objid = c.oid AND "
                            "d.objsubid = 0 AND "
                            "d.refclassid = c.tableoid AND d.deptype = 'a') "
!                           "WHERE relkind in ('%c', '%c', '%c', '%c') "
                            "ORDER BY c.oid",
                            username_subquery,
                            RELKIND_SEQUENCE,
--- 3998,4005 ----
                            "d.classid = c.tableoid AND d.objid = c.oid AND "
                            "d.objsubid = 0 AND "
                            "d.refclassid = c.tableoid AND d.deptype = 'a') "
!                        "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) "
!                           "WHERE c.relkind in ('%c', '%c', '%c', '%c') "
                            "ORDER BY c.oid",
                            username_subquery,
                            RELKIND_SEQUENCE,