On 1/1/14, 12:38 PM, "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote:
>"Reiser, John J." <Reiser@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
