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

I believe the history of this cluster is that it started on 9.0 and was upgraded to 9.1 via pg_upgrade. The instance
I'mworking on was created as a streaming replica, then I broke the replication to make it a standalone master
specificallyfor testing pg_upgrade to 9.2.  

On May 9, 2013, at 5:29 PM, Bruce Momjian <bruce@momjian.us> wrote:

> On Thu, May  9, 2013 at 05:11:43PM -0400, Tom Lane wrote:
>> Bruce Momjian <bruce@momjian.us> writes:
>>> OK, that's progress.  Having received the table schema privately via
>>> email, I see several 'character varying(40)' fields in the schema.  So
>>> the question is how was this table able to get away without a TOAST
>>> table in 9.1, while 9.2 created one for an empty table?  Ideas?
>>
>> AFAICT the needs_toast_table() logic is identical between 9.1 and 9.2,
>> so it seems like it must have something to do with an odd ALTER TABLE
>> history in the source database.  It's hard to think what, however.
>>
>> In any case, it seems like pg_upgrade ought to have a strategy for
>> dealing with tables acquiring toast tables like this, since if we
>> ever do tweak the needs_toast_table() logic, or for instance do
>> something like deciding to support 6-byte UTF8 codes, we're going
>> to face such cases.  I dunno exactly how we might deal with it though...
>
> Well, pg_upgrade operates in super-paranoid mode, so if we relax this,
> it could potentially allow silent upgrade failures.  I realize
> eventually we will need to deal with this, but I would prefer to delay
> that.
>
> Also, I added code in PG 9.1 to allow the old/new clusters to have
> identical OID layouts, so this would certainly complicate the code;  see
> info.c::gen_db_file_maps() for the check that is failing, and you can
> see the 1:1 relationship.  It was done in this commit:
>
>    commit 002c105a0706bd1c1e939fe0f47ecdceeae6c52d
>    Author: Bruce Momjian <bruce@momjian.us>
>    Date:   Sat Jan 8 13:44:44 2011 -0500
>
>        In pg_upgrade, remove functions that did sequential array scans looking
>        up relations, but rather order old/new relations and use the same array
>        index value for both.  This should speed up pg_upgrade for databases
>        with many relations.
>
> FYI, historically we have fixed TOAST table creation issues in pg_dump.
>
> Evan, is the 9.1 cluster loaded into 9.1 or did you use pg_upgrade
> previously to upgrade it _to_ 9.1?
>
> --
>  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + It's impossible for everything to be true. +