Just a footnote. This is not a postgis object, it's an esri object. Belongs to
the sde schema that is installed in every database where esri software is
installed in, namely arcgis server.
-----Mensagem original-----
De: Duarte Carreira
Enviada: 25 de janeiro de 2019 18:03
Para: 'Tom Lane' <t...@sss.pgh.pa.us>
Cc: pgsql-gene...@postgresql.org
Assunto: RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5
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 <t...@sss.pgh.pa.us>
Enviada: 25 de janeiro de 2019 16:04
Para: Duarte Carreira <dcarre...@edia.pt>
Cc: pgsql-gene...@postgresql.org
Assunto: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5
Duarte Carreira <dcarre...@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
