Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Duarte Carreira <DCarreira@edia.pt> writes:
> Sure, I'll be sending you the dump shortly off-list.

Hmph.  So the problem seems to be that pg_dump isn't emitting a "shell
type" command for sde.st_envelope.  The first mention of that type is
in the "CREATE FUNCTION sde.st_envelope_in..." command, and that won't
have done anything to force the correct type OID to be assigned, and
indeed what you get happens to conflict with some other type.

Whatever the problem is only affects that one type --- if you grep
the pg_dump output for CREATE TYPE, you see

CREATE TYPE sde.se_coord;
CREATE TYPE sde.se_coord (
CREATE TYPE sde.se_extent;
CREATE TYPE sde.se_extent (
CREATE TYPE sde.st_envelope (
CREATE TYPE sde.st_geometry;
CREATE TYPE sde.st_geometry (
CREATE TYPE sde.st_pixeldata;
CREATE TYPE sde.st_pixeldata (
CREATE TYPE sde.st_raster;
CREATE TYPE sde.st_raster (
CREATE TYPE sde.st_state_data_type;
CREATE TYPE sde.st_state_data_type (

The lines with just a type name and no parameter list are the shell
type creation commands, and there's one for each base type ...
except st_envelope.

I'm not very sure what's going on here, but the logic that pg_dump
uses to decide whether to print a shell type assumes that there will
be circular dependencies between the base type and its I/O functions.
I wonder whether pg_depend has gotten damaged in your source database.

One thing you could check to start with is to manually try pg_dump's
dependency-fetch query in the problematic database:

explain SELECT classid, objid, refclassid, refobjid, deptype
FROM pg_depend WHERE deptype != 'p' AND deptype != 'e' ORDER BY 1,2;

If you get an indexscan plan, then it's possible that the issue
is a corrupted index, and "REINDEX pg_depend" would fix it.

But I suspect that it's going to be a seqscan-and-sort, which would mean
that the indexes couldn't be at fault and there actually is missing data
in pg_depend.  (The 9.3 release series had some messy data-loss problems,
so this conclusion isn't as astonishing as one could wish.)

The next thing to look at would be the dependencies associated with
the st_envelope type.  Try queries like this:

regression=# select pg_describe_object(refclassid,refobjid,refobjsubid), deptype from pg_depend where objid =
'public.widget'::regtype;
     pg_describe_object      | deptype
-----------------------------+---------
 schema public               | n
 function widget_in(cstring) | n
 function widget_out(widget) | n
(3 rows)

regression=# select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where refobjid =
'public.widget'::regtype;
         pg_describe_object          | deptype
-------------------------------------+---------
 function widget_in(cstring)         | n
 function widget_out(widget)         | n
 type widget[]                       | i
 ... more stuff ...

I don't have postgis installed here, so this example is looking at
a type "public.widget", but of course what you want to look at is
sde.st_envelope.  If you don't see links to st_envelope_in and
st_envelope_out in both queries, then we've found the problem.

Fixing it is a bit trickier, but in principle you could manually
insert the missing row(s) once you know what they need to be.

            regards, tom lane