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

On 5/23/14, 7:36 AM, Andres Freund wrote:

>
> Any chance you could, *before* you create the toast table, do a:
> SELECT attrelid::regclass, attname, attnum, attlen, *
> FROM pg_attribute
> WHERE attrelid = 'a'::regclass
> ORDER BY attnum ASC;
>
> Where 'a' is replaced by the affected table?
>
> Greetings,
>
> Andres Freund
>

Yes, here you go:

postgres(at)vdev1commandprompt2:~$ psql -p 5433 UDB
psql (9.3.4, server 8.4.21)
Type "help" for help.

UDB=# \x
Expanded display is on.
UDB=# SELECT attrelid::regclass, attname, attnum, attlen, *
FROM pg_attribute
WHERE attrelid = 'masterairportlist'::regclass
ORDER BY attnum ASC;
UDB=#
[ RECORD 1 ]-+------------------
attrelid | masterairportlist
attname | tableoid
attnum | -7
attlen | 4
attrelid | 18098
attname | tableoid
atttypid | 26
attstattarget | 0
attlen | 4
attnum | -7
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | i
attnotnull | t
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 2 ]-+------------------
attrelid | masterairportlist
attname | cmax
attnum | -6
attlen | 4
attrelid | 18098
attname | cmax
atttypid | 29
attstattarget | 0
attlen | 4
attnum | -6
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | i
attnotnull | t
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 3 ]-+------------------
attrelid | masterairportlist
attname | xmax
attnum | -5
attlen | 4
attrelid | 18098
attname | xmax
atttypid | 28
attstattarget | 0
attlen | 4
attnum | -5
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | i
attnotnull | t
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 4 ]-+------------------
attrelid | masterairportlist
attname | cmin
attnum | -4
attlen | 4
attrelid | 18098
attname | cmin
atttypid | 29
attstattarget | 0
attlen | 4
attnum | -4
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | i
attnotnull | t
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 5 ]-+------------------
attrelid | masterairportlist
attname | xmin
attnum | -3
attlen | 4
attrelid | 18098
attname | xmin
atttypid | 28
attstattarget | 0
attlen | 4
attnum | -3
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | i
attnotnull | t
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 6 ]-+------------------
attrelid | masterairportlist
attname | ctid
attnum | -1
attlen | 6
attrelid | 18098
attname | ctid
atttypid | 27
attstattarget | 0
attlen | 6
attnum | -1
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | f
attstorage | p
attalign | s
attnotnull | t
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 7 ]-+------------------
attrelid | masterairportlist
attname | id
attnum | 1
attlen | 4
attrelid | 18098
attname | id
atttypid | 23
attstattarget | -1
attlen | 4
attnum | 1
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | i
attnotnull | t
atthasdef | t
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 8 ]-+------------------
attrelid | masterairportlist
attname | airport_code
attnum | 2
attlen | -1
attrelid | 18098
attname | airport_code
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 2
attndims | 0
attcacheoff | -1
atttypmod | 7
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 9 ]-+------------------
attrelid | masterairportlist
attname | airport_name
attnum | 3
attlen | -1
attrelid | 18098
attname | airport_name
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 3
attndims | 0
attcacheoff | -1
atttypmod | 68
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 10 ]+------------------
attrelid | masterairportlist
attname | city_name
attnum | 4
attlen | -1
attrelid | 18098
attname | city_name
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 4
attndims | 0
attcacheoff | -1
atttypmod | 68
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 11 ]+------------------
attrelid | masterairportlist
attname | confirm
attnum | 5
attlen | 1
attrelid | 18098
attname | confirm
atttypid | 16
attstattarget | -1
attlen | 1
attnum | 5
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | c
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 12 ]+------------------
attrelid | masterairportlist
attname | country_code
attnum | 6
attlen | -1
attrelid | 18098
attname | country_code
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 6
attndims | 0
attcacheoff | -1
atttypmod | 6
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 13 ]+------------------
attrelid | masterairportlist
attname | country_name
attnum | 7
attlen | -1
attrelid | 18098
attname | country_name
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 7
attndims | 0
attcacheoff | -1
atttypmod | 68
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 14 ]+------------------
attrelid | masterairportlist
attname | destrank
attnum | 8
attlen | 4
attrelid | 18098
attname | destrank
atttypid | 23
attstattarget | -1
attlen | 4
attnum | 8
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 15 ]+------------------
attrelid | masterairportlist
attname | dstflag
attnum | 9
attlen | 1
attrelid | 18098
attname | dstflag
atttypid | 16
attstattarget | -1
attlen | 1
attnum | 9
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | c
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 16 ]+------------------
attrelid | masterairportlist
attname | enddst
attnum | 10
attlen | 8
attrelid | 18098
attname | enddst
atttypid | 1114
attstattarget | -1
attlen | 8
attnum | 10
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | d
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 17 ]+------------------
attrelid | masterairportlist
attname | fr_airport_name
attnum | 11
attlen | -1
attrelid | 18098
attname | fr_airport_name
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 11
attndims | 0
attcacheoff | -1
atttypmod | 260
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 18 ]+------------------
attrelid | masterairportlist
attname | fr_city_name
attnum | 12
attlen | -1
attrelid | 18098
attname | fr_city_name
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 12
attndims | 0
attcacheoff | -1
atttypmod | 68
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 19 ]+------------------
attrelid | masterairportlist
attname | fr_country_code
attnum | 13
attlen | -1
attrelid | 18098
attname | fr_country_code
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 13
attndims | 0
attcacheoff | -1
atttypmod | 6
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 20 ]+------------------
attrelid | masterairportlist
attname | fr_country_name
attnum | 14
attlen | -1
attrelid | 18098
attname | fr_country_name
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 14
attndims | 0
attcacheoff | -1
atttypmod | 68
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 21 ]+------------------
attrelid | masterairportlist
attname | fr_prov_state
attnum | 15
attlen | -1
attrelid | 18098
attname | fr_prov_state
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 15
attndims | 0
attcacheoff | -1
atttypmod | 68
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 22 ]+------------------
attrelid | masterairportlist
attname | origrank
attnum | 16
attlen | 4
attrelid | 18098
attname | origrank
atttypid | 23
attstattarget | -1
attlen | 4
attnum | 16
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 23 ]+------------------
attrelid | masterairportlist
attname | prov_code
attnum | 17
attlen | -1
attrelid | 18098
attname | prov_code
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 17
attndims | 0
attcacheoff | -1
atttypmod | 104
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 24 ]+------------------
attrelid | masterairportlist
attname | prov_name
attnum | 18
attlen | -1
attrelid | 18098
attname | prov_name
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 18
attndims | 0
attcacheoff | -1
atttypmod | 104
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 25 ]+------------------
attrelid | masterairportlist
attname | prov_state
attnum | 19
attlen | -1
attrelid | 18098
attname | prov_state
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 19
attndims | 0
attcacheoff | -1
atttypmod | 68
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 26 ]+------------------
attrelid | masterairportlist
attname | startdst
attnum | 20
attlen | 8
attrelid | 18098
attname | startdst
atttypid | 1114
attstattarget | -1
attlen | 8
attnum | 20
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | d
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 27 ]+------------------
attrelid | masterairportlist
attname | timeoffset
attnum | 21
attlen | -1
attrelid | 18098
attname | timeoffset
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 21
attndims | 0
attcacheoff | -1
atttypmod | 104
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |
-[ RECORD 28 ]+------------------
attrelid | masterairportlist
attname | timeref
attnum | 22
attlen | -1
attrelid | 18098
attname | timeref
atttypid | 1043
attstattarget | -1
attlen | -1
attnum | 22
attndims | 0
attcacheoff | -1
atttypmod | 104
attbyval | f
attstorage | x
attalign | i
attnotnull | f
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attacl |