Re: duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

On 1/1/14, 3:37 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>"Reiser, John J." <Reiser(at)rowan(dot)edu> writes:
>> On 1/1/14, 12:38 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> What this smells like is a bug in the pg_dump --binary_upgrade logic
>>>that
>>> tries to preserve type OIDs from the old installation to the new one.
>>> Is there a preceding CREATE TYPE command for st_envelope in the dump
>>> script? Look for calls to binary_upgrade.set_next_pg_type_oid() and
>>> binary_upgrade.set_next_array_pg_type_oid() in the dump script --- are
>>> there conflicting entries? Also, exactly what is type 1407909 in the
>>> old installation (try "select * from pg_type where oid = 1407909")?
>
>> Once I got 8.4 back up, I searched for that OID in pg_type. select *
>>from
>> pg_type where oid = 1407909; returns 0 rows.
>
>Hm, which database(s) did you check in? It certainly appears from the
>dump text you quote that type "spheroid" has OID 1407909 in at least one
>database.
>
>> I did find this, searching through pg_upgrade_restore.log. There are 8
>> instances of the following text in the file:
>
>If I'm reading you right, then these must be instances of the same type
>with the same OID declared in different databases. Could you look through
>the dump for \connect commands to verify that?
>
>> SELECT binary_upgrade.set_next_pg_type_oid('1407909'::pg_catalog.oid);
>> occurs 22 times on lines (1150, 1176, 44192, 44218, 64149, 64175, 71815,
>> 71841, 79844, 79870, 88982, 89008, 97153, 97179, 106523, 106549, 289254,
>> 289280, 297653, 297679, 310824, 310850) and all the close pairs are 26
>> lines apart, like the excerpt copied above.
>
>Could you look at the text surrounding these places to determine which
>types this OID is being selected for? Each of these calls should be just
>preceding a CREATE TYPE command (with maybe a set_next_array_pg_type_oid
>call between) that is supposed to use the specified OID for its type.
>Also identify which databases the commands are being issued in, by looking
>back for the most recent \connect command.
>
>Also, is there any CREATE TYPE for st_envelope preceding the failing
>CREATE FUNCTION command (in the same database)?
>
> regards, tom lane

Tom,

Thanks for the info. After searching the output for the connection string,
I found that it's failing on a database that can be archived. I think I'll
get what I need from the database, drop it, then perform the upgrade.

Thank you again for all of your help. It's greatly appreciated!

John