Re: pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

On Wed, May 21, 2014 at 04:37:13PM -0400, Bruce Momjian wrote:
> On Wed, May 21, 2014 at 04:23:34PM -0400, Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > On Wed, May 21, 2014 at 10:56:59AM -0600, Jeff Ross wrote:
> > >> Yes, using 9.3's pg_dump of 8.4 and applying it to both an 8.4 and a
> > >> 9.3 cluster, the contact_email table now has toast in both 8.4 and
> > >> 9.3.
> > 
> > > So the big question is why doesn't the existing 8.4 table have a toast
> > > table?  Did you use pg_upgrade on the old cluster before, e.g. from 8.3?
> > 
> > I have a vague recollection that we changed the calculations about whether
> > a toast table would be needed, but I can't find it in the commit logs
> > right now --- and anyway I think this was pre-8.4.
> 
> The only item I can think of that would cause this is someone changing
> the length of a string.  Did someone modify pg_attribute directly to
> increase the length of one of the character columns?  
> 
> I just tested ALTER TABLE in 8.4 and it does create a toast table for
> this case in 9.4:
> 
>     CREATE TABLE test (x CHAR(10));
>     ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000);

Has anyone changed the database encoding by modifying the system
catalogs?  That might cause the problem too.

Moving forward, I think you need to add a dummy column to each problem
table and drop the column ---- that will create a toast table and allow
you to do the upgrade.  I could have pg_upgrade detect this problem, but
until I know the cause, I don't think that is wise.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +