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

On Thu, May 9, 2013 at 03:52:42PM -0400, Evan D. Hoffman wrote:
> That's correct. Here's what substitutionlist_pkey looks like in the new
> cluster. From this, it looks like it's actually correct (the oid for
> substitutionlist_pkey is correct) but pg_upgrade thinks it's wrong and dies.
> I'll look for the logs you requested and send them separately
>
> db=# SELECT relname, relfilenode, relkind from pg_class where oid = 299749;
> relname | relfilenode | relkind
> ----------------+-------------+---------
> pg_toast_17304 | 299749 | t
> (1 row)
>
> db=# select oid, relname, relfilenode, pg_relation_filepath(oid), relkind from
> pg_class where relname='substitutionlist_pkey';
> oid | relname | relfilenode | pg_relation_filepath | relkind
>
> ---------+-----------------------+-------------+----------------------+---------
> 2938685 | substitutionlist_pkey | 2938685 | base/16488/2938685 | i
> (1 row)
>
> db=# select version();
>>
> PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-3), 6
> 4-bit
> (1 row)

OK, that is very helpful. I am now wondering if the problem is that 9.2
has created a toast table for a 9.1 table that didn't have one. Can you
run this query on 9.2:

SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
WHERE reltoastrelid = 299749;

(I think its oid will be 17304 based on the toast name.) Then, in the
9.1 cluster, using the 'oid' mentioned above, show me:

SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class
WHERE oid = 'oid_from_above';

If its 'reltoastrelid' is zero, that means 9.2 has a toast table while
9.1 did not have one, and we then need to find out why. I would need to
see the schema of that table. For TOAST details, see:

http://momjian.us/main/blogs/pgblog/2012.html#January_17_2012
http://momjian.us/main/blogs/pgblog/2012.html#January_19_2012

This is the first time I am seeing this failure so I am having to ask
lots of questions.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers