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

On 5/23/14, 7:21 AM, Bruce Momjian wrote:
>
> On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote:
>>> 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);
>>>
>> I just tried this on the problem table and it did indeed create a
>> toast table.
>>
>> I then retried pg_upgrade and it failed with the same problem on a
>> different table in the same database. Of the 67 databases in the
>> 8.4 cluster, 5 (so far) have had this problem on at least one table.
>
> Yeah, it would be nice to be able to report all the problem tables, but
> I don't know how to do that except from pg_upgrade failing. Is there
> anything similar about these tables?
>

Here are the last 2 tables I had a problem with:

UDB=# \d contact_email
Table "public.contact_email"
Column | Type | Modifiers
--------+-----------------------------+------------------------------------------------------------
id | integer | not null default
nextval('contact_email_id_seq'::regclass)
email1 | character varying(255) | not null
email2 | character varying(255) |
time | timestamp without time zone | not null default now()
source | email_source | not null
Indexes:
"contact_email_pkey" PRIMARY KEY, btree (id)
"idx_contact_email_email1" btree (lower(email1::text)
varchar_pattern_ops)
"idx_contact_email_email2" btree (lower(email2::text)
varchar_pattern_ops)
Referenced by:
TABLE "abandoned_master_booking" CONSTRAINT
"abandoned_master_booking_contact_email_id_fkey" FOREIGN KEY
(contact_email_id) REFERENCES contact_email(id)
TABLE "contact" CONSTRAINT "contact_contact_email_id_fkey" FOREIGN
KEY (contact_email_id) REFERENCES contact_email(id)
TABLE "eticketaccesslog" CONSTRAINT
"eticketaccesslog_contact_email_id_fkey" FOREIGN KEY (contact_email_id)
REFERENCES contact_email(id)

UDB=# \d masterairportlist
Table "public.masterairportlist"
Column | Type |
Modifiers
-----------------+-----------------------------+----------------------------------------------------------------
id | integer | not null default
nextval('masterairportlist_id_seq'::regclass)
airport_code | character varying(3) |
airport_name | character varying(64) |
city_name | character varying(64) |
confirm | boolean |
country_code | character varying(2) |
country_name | character varying(64) |
destrank | integer |
dstflag | boolean |
enddst | timestamp without time zone |
fr_airport_name | character varying(256) |
fr_city_name | character varying(64) |
fr_country_code | character varying(2) |
fr_country_name | character varying(64) |
fr_prov_state | character varying(64) |
origrank | integer |
prov_code | character varying(100) |
prov_name | character varying(100) |
prov_state | character varying(64) |
startdst | timestamp without time zone |
timeoffset | character varying(100) |
timeref | character varying(100) |
Indexes:
"masterairportlist_pkey" PRIMARY KEY, btree (id)
"idx_dest_cityname_conf" btree (city_name text_pattern_ops,
destrank, confirm)
"idx_orig_cityname_conf" btree (city_name text_pattern_ops,
origrank, confirm)