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@xxxxxxxxxxxxx>
Enviada: 25 de janeiro de 2019 16:04
Para: Duarte Carreira <DCarreira@xxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Assunto: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5
Duarte Carreira <DCarreira@xxxxxxx> 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
