Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4

If it's of any value, here's the create statements for the table from the
pg_upgrade logs:

--
-- Name: setupinfo; Type: TABLE; Schema: bpm; Owner: postgres; Tablespace:
--

-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('17306'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT binary_upgrade.set_next_array_pg_type_oid('17305'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_class oids
SELECT binary_upgrade.set_next_heap_pg_class_oid('17304'::pg_catalog.oid);

CREATE TABLE setupinfo (
id1 bigint NOT NULL,
cl2 bigint NOT NULL,
re3 character varying(40),
re4 character varying(40),
re5 character varying(40),
ft6 character varying(40),
ft7 character varying(40),
ft8 character varying(40),
sf9 boolean DEFAULT false,
on10 character varying(20),
we11 boolean DEFAULT false,
en12 character varying(100),
en13 character varying(100),
cs14 date,
pr15 date,
"........pg.dropped.16........" INTEGER /* dummy */,
"........pg.dropped.17........" INTEGER /* dummy */,
"........pg.dropped.18........" INTEGER /* dummy */,
"........pg.dropped.19........" INTEGER /* dummy */,
"........pg.dropped.20........" INTEGER /* dummy */,
"........pg.dropped.21........" INTEGER /* dummy */,
"........pg.dropped.22........" INTEGER /* dummy */,
"........pg.dropped.23........" INTEGER /* dummy */,
"........pg.dropped.24........" INTEGER /* dummy */,
"........pg.dropped.25........" INTEGER /* dummy */,
"........pg.dropped.26........" INTEGER /* dummy */,
ha27 character varying(10) DEFAULT 'UNKNOWN'::character varying,
"........pg.dropped.28........" INTEGER /* dummy */,
dr29 character varying(10)
);

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = 1, attalign = 'c', attbyval = false
WHERE attname = '........pg.dropped.16........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.16........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.17........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.17........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.18........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.18........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.19........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.19........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.20........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.20........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.21........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.21........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.22........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.22........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.23........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.23........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.24........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.24........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.25........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.25........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = -1, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.26........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.26........";

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = 1, attalign = 'c', attbyval = false
WHERE attname = '........pg.dropped.28........'
AND attrelid = 'setupinfo'::pg_catalog.regclass;
ALTER TABLE ONLY setupinfo DROP COLUMN "........pg.dropped.28........";

-- For binary upgrade, set heap's relfrozenxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '318630027'
WHERE oid = 'setupinfo'::pg_catalog.regclass;

ALTER TABLE bpm.setupinfo OWNER TO postgres;

SET search_path = analytics, pg_catalog;

--

On Fri, May 10, 2013 at 7:30 AM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:

> On 2013-05-10 07:25:35 -0400, Bruce Momjian wrote:
> > On Thu, May 9, 2013 at 06:19:31PM -0400, Bruce Momjian wrote:
> > > > pg_upgrade already deals with the new code deciding not to create a
> > > > toast table (by forcing it to do so anyway in binary upgrade mode).
> > >
> > > Yes, a good point I had forgotten. postgres --binary-upgrade mode can
> > > force the toast table to be created to match the old cluster; see
> > > toasting.c::create_toast_table():
> > >
> > > /*
> > > * Check to see whether the table actually needs a TOAST table.
> > > *
> > > * If an update-in-place toast relfilenode is specified, force
> toast file
> > > * creation even if it seems not to need one.
> > > */
> > > if (!needs_toast_table(rel) &&
> > > (!IsBinaryUpgrade ||
> > > !OidIsValid(binary_upgrade_next_toast_pg_class_oid)))
> > > return false;
> > >
> > > > It's only the other case that's problematic -- but then AFAICS fixing
> > > > that is just a SMOP.
> > >
> > > Yes, it is this opposite case where the _new_ cluster wants a TOAST
> > > table that the old cluster doesn't have, which is what Evan is
> > > reporting.
> >
> > So, if we eventually agree we need to be able to _suppress_ creation of
> > the TOAST table on the new cluster, I propose we do it in a similar way
> > to how we force TOAST creation, by having pg_dump set a backend variable
> > that is then tested in the backend to suppress TOAST table creation.
>
> I don't think disregarding the new clusters ideas about the requirement
> of a toast table is a good idea; far too likely to cause problems in the
> future.
> So if there is a valid case where this can happen - which I am far from
> sure from what I skimmed so far - we need a) a way to get a toast oid
> that doesn't conflict with any of the oids in the old cluster b)
> pg_upgrade then needs to accept that the new cluster might have more
> toast rels than the old version.
>
> > I don't think we know enough about the cause of this pg_upgrade failure
> > to know if this is necessary.
>
> True.
>
> Greetings,
>
> Andres Freund
>
> --
> Andres Freund http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>