Home > mailing lists

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

From	Evan D. Hoffman
Subject	Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4
Date	May 10, 2013 19:36:29
Msg-id	CABRB-LuQkW_B7H08G4U2b0ZkuUJ+FEq1eyVanPNHxGPy89KExg@mail.gmail.com Whole thread Raw
In response to	Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4 (Andres Freund <andres@2ndquadrant.com>)
Responses	Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4 (Bruce Momjian <bruce@momjian.us>)
List	pgsql-hackers

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@2ndquadrant.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

pgsql-hackers by date:

Previous

From: Amit Kapila
Date: 10 May 2013, 19:32:19
Subject: Re: corrupt pages detected by enabling checksums

Next

From: Fabien COELHO
Date: 10 May 2013, 19:40:42
Subject: Re: [PATCH] Make "psql -1 < file.sql" work as with "-f"