RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5 - Mailing list pgsql-general

First, I'd like to apologize for posting the dump file to the whole list... I wish I could delete it, at least the
attachment...

I ran the explain plan, and got a seq scan, so no faulty index:
"Sort  (cost=5073.45..5195.00 rows=48622 width=17)"
"  Sort Key: classid, objid"
"  ->  Seq Scan on pg_depend  (cost=0.00..1288.39 rows=48622 width=17)"
"        Filter: ((deptype <> 'p'::"char") AND (deptype <> 'e'::"char"))"

So I proceeded with the 2 queries:

select pg_describe_object(refclassid,refobjid,refobjsubid), deptype from pg_depend where objid =
'sde.st_envelope'::regtype;
"schema sde";"n"
"function st_envelope_in(cstring)";"n"
"function st_envelope_out(st_envelope)";"n"

select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where refobjid = 'sde.st_envelope'::regtype;
"type st_envelope[]";"i"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"

But I couldn't figure out what this means...

Duarte

-----Mensagem original-----
De: Tom Lane <tgl@sss.pgh.pa.us>
Enviada: 25 de janeiro de 2019 16:04
Para: Duarte Carreira <DCarreira@edia.pt>
Cc: pgsql-general@postgresql.org
Assunto: 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
mentionof that type is in the "CREATE FUNCTION sde.st_envelope_in..." command, and that won't have done anything to
forcethe 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
basetype ... 
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
thatthere 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'
ORDERBY 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
thereactually is missing data in pg_depend.  (The 9.3 release series had some messy data-loss problems, so this
conclusionisn'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
tolook 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
foundthe problem. 

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

            regards, tom lane