Обсуждение: duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope

    LANGUAGE "c" IMMUTABLE STRICT

    AS 'st_geometry', 'ST_ENVELOPE_In';

psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates unique constraint "pg_type_oid_index"

DETAIL:  Key (oid)=(1407909) already exists.

On 01/01/2014 08:53 AM, Reiser, John J. wrote:
> Hello,
>
> I'm working on an upgrade to our database cluster, attempting to move
> from 8.4 to 9.2. I'm encountering the following error when I attempt the
> upgrade (in pg_upgrade_restore.log):
>
>     CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
>          LANGUAGE "c" IMMUTABLE STRICT
>          AS 'st_geometry', 'ST_ENVELOPE_In';
>     psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value
>     violates unique constraint "pg_type_oid_index"
>     DETAIL:  Key (oid)=() already exists.

Well this means an OID is being used twice in the system catalog
pg_type. You could look up that oid(1407909) in the 8.4 pg_type and see
what it is. Also look it up in the fresh pg_type when you init the 9.2
cluster.

>
> I'm running this on CentOS 6; both 8.4 and 9.2 are installed from the
> Yum repository. PostgreSQL is primarily used for GIS data and has ESRI
> st_geometry and PostGIS installed in several of the databases. (ESRI's
> support is only up to 9.2, which is why I'm not attempting a move to
> 9.3.) The interesting thing with this error is that when I wipe out the
> 9.2 data directory, re-initdb, and run the upgrade again, I now get a
> different error:
>
>     CREATE TABLESPACE "sde1" OWNER "sde" LOCATION '/disk2/pgsql/data/sde';
>     psql:pg_upgrade_dump_globals.sql:294: ERROR:  directory
>     "/disk2/pgsql/data/sde/PG_9.2_201204301" already in use as a tablespace

When you ran the upgrade above it probably got as far as creating the
9.2 tablespaces in /disk2/pgsql/data/sde. You now have two versions of
the tablespaces, one labeled PG_8.4_* and the other PG_9.2_201204301.
Along with wiping out the 9.2 data directory you need to wipe out the
9.2 tablespace directory.

>
>
> (I have several of our ESRI SDE databases in their own tablespace.)
>
> Before starting this process, I made a complete file-based backup of the
> 8.4 data directory. When I restore the backup to /var/lib/pgsql and run
> pg_upgrade again, I receive the first error again, with the same exact
> OID value. I will admit I don't know much about Postgres internals and
> I'm not sure how to proceed with this duplicate OID issue.
>
> I'm going to try running pg_upgrade with the link option now, but I
> don't know if that will help.
>
> Any assistance provided would be greatly appreciated.
>
> Thanks,
> John
>
> John Reiser
>
> /Geospatial Research Lab <http://gis.rowan.edu/>/
>
> *Rowan University <http://rowan.edu/geography>*
>
> 201 Mullica Hill Road
>
> Glassboro, NJ 08028
>
> phone: 856-256-4817
>
> cell: 856-347-0047
>
> twitter: @rowangeolab <http://twitter.com/rowangeolab>
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 01/01/2014 09:08 AM, Reiser, John J. wrote:
> The --link argument doesn't work, either:
>

>
> Consult the last few lines of "pg_upgrade_restore.log" for
> the probable cause of the failure.
> Failure, exiting
> bash-4.1$ tail -n 20 pg_upgrade_restore.log
> (1 row)
>
> CREATE TYPE "spheroid" (
>      INTERNALLENGTH = 65,
>      INPUT = spheroid_in,
>      OUTPUT = spheroid_out,
>      ALIGNMENT = double,
>      STORAGE = plain
> );
> CREATE TYPE
> ALTER TYPE "public"."spheroid" OWNER TO "reiser";
> ALTER TYPE
> SET search_path = "sde", pg_catalog;
> SET
> CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
>      LANGUAGE "c" IMMUTABLE STRICT
>      AS 'st_geometry', 'ST_ENVELOPE_In';
> psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates
> unique constraint "pg_type_oid_index"
> DETAIL:  Key (oid)=(1407909) already exists.
>
> Again, any help that you could provide would be greatly appreciated.

So have you already installed the GIS stuff into the 9.2 cluster before
the upgrade?

> John
>


--
Adrian Klaver
adrian.klaver@gmail.com

Adrian,


On 1/1/14, 12:26 PM, "Adrian Klaver" <adrian.klaver@gmail.com> wrote:

>On 01/01/2014 09:08 AM, Reiser, John J. wrote:
>> The --link argument doesn't work, either:
>>
>
>>
>> Consult the last few lines of "pg_upgrade_restore.log" for
>> the probable cause of the failure.
>> Failure, exiting
>> bash-4.1$ tail -n 20 pg_upgrade_restore.log
>> (1 row)
>>
>> CREATE TYPE "spheroid" (
>>      INTERNALLENGTH = 65,
>>      INPUT = spheroid_in,
>>      OUTPUT = spheroid_out,
>>      ALIGNMENT = double,
>>      STORAGE = plain
>> );
>> CREATE TYPE
>> ALTER TYPE "public"."spheroid" OWNER TO "reiser";
>> ALTER TYPE
>> SET search_path = "sde", pg_catalog;
>> SET
>> CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
>>      LANGUAGE "c" IMMUTABLE STRICT
>>      AS 'st_geometry', 'ST_ENVELOPE_In';
>> psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates
>> unique constraint "pg_type_oid_index"
>> DETAIL:  Key (oid)=(1407909) already exists.
>>
>> Again, any help that you could provide would be greatly appreciated.
>
>So have you already installed the GIS stuff into the 9.2 cluster before
>the upgrade?

I have installed PostGIS 1.5.8 and the st_geometry.so file that ESRI
requires into the 9.2 directory (/usr/pgsql-9.2/lib). pg_upgrade wouldn't
proceed as far as it did without doing that.

I received your other email and will try the upgrade again and compare the
OIDs in new and old. I'll email the list again once I've done that.

Thanks again for your help.


>> John
>>
>
>
>--
>Adrian Klaver
>adrian.klaver@gmail.com

"Reiser, John J." <Reiser@rowan.edu> writes:
> I'm working on an upgrade to our database cluster, attempting to move from 8.4 to 9.2. I'm encountering the following
errorwhen I attempt the upgrade (in pg_upgrade_restore.log): 

> CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
>     LANGUAGE "c" IMMUTABLE STRICT
>     AS 'st_geometry', 'ST_ENVELOPE_In';
> psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates unique constraint "pg_type_oid_index"
> DETAIL:  Key (oid)=(1407909) already exists.

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")?

> I'm running this on CentOS 6; both 8.4 and 9.2 are installed from the Yum repository. PostgreSQL is primarily used
forGIS data and has ESRI st_geometry and PostGIS installed in several of the databases. (ESRI's support is only up to
9.2,which is why I'm not attempting a move to 9.3.) The interesting thing with this error is that when I wipe out the
9.2data directory, re-initdb, and run the upgrade again, I now get a different error: 

> CREATE TABLESPACE "sde1" OWNER "sde" LOCATION '/disk2/pgsql/data/sde';
> psql:pg_upgrade_dump_globals.sql:294: ERROR:  directory "/disk2/pgsql/data/sde/PG_9.2_201204301" already in use as a
tablespace

I think that's pilot error: you forgot to clean out tablespace directories
along with the main data directory.

            regards, tom lane

On 1/1/14, 12:38 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

>"Reiser, John J." <Reiser@rowan.edu> writes:
>> I'm working on an upgrade to our database cluster, attempting to move
>>from 8.4 to 9.2. I'm encountering the following error when I attempt the
>>upgrade (in pg_upgrade_restore.log):
>
>> CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
>>     LANGUAGE "c" IMMUTABLE STRICT
>>     AS 'st_geometry', 'ST_ENVELOPE_In';
>> psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value
>>violates unique constraint "pg_type_oid_index"
>> DETAIL:  Key (oid)=(1407909) already exists.
>
>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.

I did find this, searching through pg_upgrade_restore.log. There are 8
instances of the following text in the file:

CREATE TYPE "pgis_abs" (
    INTERNALLENGTH = 8,
    INPUT = pgis_abs_in,
    OUTPUT = pgis_abs_out,
    ALIGNMENT = double,
    STORAGE = plain
);
CREATE TYPE
ALTER TYPE "public"."pgis_abs" OWNER TO "reiser";
ALTER TYPE
SELECT binary_upgrade.set_next_pg_type_oid('1407909'::pg_catalog.oid);
 set_next_pg_type_oid
----------------------

(1 row)

SELECT
binary_upgrade.set_next_array_pg_type_oid('1407914'::pg_catalog.oid);
 set_next_array_pg_type_oid
----------------------------

(1 row)

CREATE TYPE "spheroid";
CREATE TYPE
CREATE FUNCTION "spheroid_in"(cstring) RETURNS spheroid
    LANGUAGE "c" IMMUTABLE STRICT
    AS '$libdir/postgis-1.5', 'ellipsoid_in';
CREATE FUNCTION
ALTER FUNCTION "public"."spheroid_in"(cstring) OWNER TO "reiser";
ALTER FUNCTION
CREATE FUNCTION "spheroid_out"(spheroid) RETURNS cstring
    LANGUAGE "c" IMMUTABLE STRICT
    AS '$libdir/postgis-1.5', 'ellipsoid_out';
CREATE FUNCTION
ALTER FUNCTION "public"."spheroid_out"(spheroid) OWNER TO "reiser";
ALTER FUNCTION
SELECT binary_upgrade.set_next_pg_type_oid('1407909'::pg_catalog.oid);
 set_next_pg_type_oid
----------------------

(1 row)

SELECT
binary_upgrade.set_next_array_pg_type_oid('1407914'::pg_catalog.oid);
 set_next_array_pg_type_oid
----------------------------

(1 row)

CREATE TYPE "spheroid" (
    INTERNALLENGTH = 65,
    INPUT = spheroid_in,
    OUTPUT = spheroid_out,
    ALIGNMENT = double,
    STORAGE = plain
);
CREATE TYPE
ALTER TYPE "public"."spheroid" OWNER TO "reiser";
ALTER TYPE
CREATE FUNCTION "_st_asgeojson"(integer, geometry, integer, integer)
RETURNS text
    LANGUAGE "c" IMMUTABLE STRICT
    AS '$libdir/postgis-1.5', 'LWGEOM_asGeoJson';
CREATE FUNCTION
ALTER FUNCTION "public"."_st_asgeojson"(integer, geometry, integer,
integer) OWNER TO "reiser";
ALTER FUNCTION





The end of the file differs in that creation of the st_envelope_in
function is attempted instead of _st_asgeojson.

CREATE FUNCTION "st_envelope_in"
is only in the file 5 times (one being just before the error) and here it
is in context:

SET search_path = "sde", pg_catalog;
SET
CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
    LANGUAGE "c" IMMUTABLE STRICT
    AS 'st_geometry', 'ST_ENVELOPE_In';
CREATE FUNCTION
ALTER FUNCTION "sde"."st_envelope_in"(cstring) OWNER TO "sde";
ALTER FUNCTION

The line
CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
occurs on lines 9076, 106654, 139095, 164850 and 310874 (the error)

and
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.


Any insight you can provide would be greatly appreciated.

Thanks,
John


>
>> I'm running this on CentOS 6; both 8.4 and 9.2 are installed from the
>>Yum repository. PostgreSQL is primarily used for GIS data and has ESRI
>>st_geometry and PostGIS installed in several of the databases. (ESRI's
>>support is only up to 9.2, which is why I'm not attempting a move to
>>9.3.) The interesting thing with this error is that when I wipe out the
>>9.2 data directory, re-initdb, and run the upgrade again, I now get a
>>different error:
>
>> CREATE TABLESPACE "sde1" OWNER "sde" LOCATION '/disk2/pgsql/data/sde';
>> psql:pg_upgrade_dump_globals.sql:294: ERROR:  directory
>>"/disk2/pgsql/data/sde/PG_9.2_201204301" already in use as a tablespace
>
>I think that's pilot error: you forgot to clean out tablespace directories
>along with the main data directory.
>
>            regards, tom lane

"Reiser, John J." <Reiser@rowan.edu> writes:
> On 1/1/14, 12:38 PM, "Tom Lane" <tgl@sss.pgh.pa.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

On 1/1/14, 3:37 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

>"Reiser, John J." <Reiser@rowan.edu> writes:
>> On 1/1/14, 12:38 PM, "Tom Lane" <tgl@sss.pgh.pa.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

On 01/01/2014 12:45 PM, Reiser, John J. wrote:
>
> On 1/1/14, 3:37 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>

>>
>> 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.

Well I think Tom was looking for that information to not only solve your
issue but also to determine whether this is a bug that needs to be
fixed. So providing that information would help the greater cause if it
is something you can do.

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


--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 01/01/2014 12:45 PM, Reiser, John J. wrote:
>> 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.

> Well I think Tom was looking for that information to not only solve your
> issue but also to determine whether this is a bug that needs to be
> fixed. So providing that information would help the greater cause if it
> is something you can do.

Yes, exactly --- I think this probably indicates a previously-unknown
bug in the pg_upgrade process.

If you still have the pg_upgrade_dump_NNN.custom file corresponding to the
failing database, and don't mind showing someone else your schema
definitions (not table data, it doesn't contain any of that), would you be
willing to send me that file off-list?

            regards, tom lane