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                |
notnull
 
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    |
charactervarying(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 |
charactervarying(2)        | fr_country_name | character varying(64)       | fr_prov_state   | character varying(64)
  | origrank        | integer                     | prov_code       | character varying(100)      | prov_name       |
charactervarying(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)