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

Duarte Carreira <DCarreira@edia.pt> writes:
> I've trying to upgrade a 9.3 instance to 9.5 using pg_upgrade and facing this issue...

9.5.what?

Perusing the commit logs, I note that 9.5.3 included a fix for a
pg_upgrade issue that could possibly lead to this symptom, see
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=196870f2f

If you are on 9.5.recent, it'd be worth looking closer, because this
is certainly pretty odd.  One wouldn't expect a CREATE FUNCTION to
result in assignment of a type OID, at least not in pg_dump/pg_upgrade
scripts --- they should always put out a shell CREATE TYPE first.

By any chance, if you attempt a "pg_dump -s" from the problematic database,
does it emit any warnings (about dependency loops, perhaps)?

            regards, tom lane

Duarte Carreira <DCarreira@edia.pt> writes:
> Tom, just did a pg_dump -s from 9.3 and there are no warnings in the output file. It goes smoothly and fast (few
seconds).Database is around 50GB, schema 9MB. Has postgis and a few more extensions - ogr_fdw (from postigs), file_fdw,
postgres_fdw,table_func. 

Hmph.  Would you be willing to send me a schema-only dump (off-list!)
of the problematic database?  The best format would be an "-Fc -s"
pg_dump output, because that would show what pg_dump thinks the
dependencies are.  Be sure you make it with the newer pg_dump.

            regards, tom lane

Sure, I'll be sending you the dump shortly off-list.

Duarte

-----Mensagem original-----
De: Tom Lane <tgl@sss.pgh.pa.us>
Enviada: 25 de janeiro de 2019 14:28
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:
> Tom, just did a pg_dump -s from 9.3 and there are no warnings in the output file. It goes smoothly and fast (few
seconds).Database is around 50GB, schema 9MB. Has postgis and a few more extensions - ogr_fdw (from postigs), file_fdw,
postgres_fdw,table_func. 

Hmph.  Would you be willing to send me a schema-only dump (off-list!) of the problematic database?  The best format
wouldbe an "-Fc -s" 
pg_dump output, because that would show what pg_dump thinks the dependencies are.  Be sure you make it with the newer
pg_dump.

            regards, tom lane

Tom,

Used this command to dump the file attached:
"D:\Program Files\PostgreSQL\9.5\bin\pg_dump.exe" -Fc -p 5432 -U postgres -W -s postgis >dump_schema_db_postgis.custom

Hope you're able to pinpoint the issue.

Thanks again.
Duarte

-----Mensagem original-----
De: Duarte Carreira
Enviada: 25 de janeiro de 2019 15:01
Para: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org
Assunto: RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Sure, I'll be sending you the dump shortly off-list.

Duarte

-----Mensagem original-----
De: Tom Lane <tgl@sss.pgh.pa.us>
Enviada: 25 de janeiro de 2019 14:28
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:
> Tom, just did a pg_dump -s from 9.3 and there are no warnings in the output file. It goes smoothly and fast (few
seconds).Database is around 50GB, schema 9MB. Has postgis and a few more extensions - ogr_fdw (from postigs), file_fdw,
postgres_fdw,table_func. 

Hmph.  Would you be willing to send me a schema-only dump (off-list!) of the problematic database?  The best format
wouldbe an "-Fc -s" 
pg_dump output, because that would show what pg_dump thinks the dependencies are.  Be sure you make it with the newer
pg_dump.

            regards, tom lane

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

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

Just a footnote. This is not a postgis object, it's an esri object. Belongs to the sde schema that is installed in
everydatabase 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' <tgl@sss.pgh.pa.us>
Cc: pgsql-general@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;
"typest_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

Duarte Carreira <DCarreira@edia.pt> writes:
> 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"

OK, that looks about like what I'd expect.

> 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"

Ah-hah --- st_envelope_in and st_envelope_out are not mentioned here?
That explains your problem.  You'd need to add those two rows to pg_depend,
which could go something like

insert into pg_depend (classid, objid, objsubid,
                       refclassid, refobjid, refobjsubid, deptype)
values (
  'pg_proc'::regclass,
  'sde.st_envelope_in(cstring)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

insert into pg_depend (classid, objid, objsubid,
                       refclassid, refobjid, refobjsubid, deptype)
values (
  'pg_proc'::regclass,
  'sde.st_envelope_out(sde.st_envelope)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

I suppose the evidence about what happened to those rows is long gone,
so there's not much point in doing anything but patching things up to
the point where you can run pg_upgrade.

            regards, tom lane

Tom, can't thank you enough.

Now, the 2nd query and results are like so:
select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where refobjid = 'sde.st_envelope'::regtype;
"function st_envelope_in(cstring)";"n"
"function st_envelope_out(st_envelope)";"n"
"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"

I'll try pg_upgrade again and let you know how it goes. (fingers crossed)

Thanks,
Duarte

-----Mensagem original-----
De: Tom Lane <tgl@sss.pgh.pa.us>
Enviada: 25 de janeiro de 2019 18:19
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:
> 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"

OK, that looks about like what I'd expect.

> 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"

Ah-hah --- st_envelope_in and st_envelope_out are not mentioned here?
That explains your problem.  You'd need to add those two rows to pg_depend, which could go something like

insert into pg_depend (classid, objid, objsubid,
                       refclassid, refobjid, refobjsubid, deptype) values (
  'pg_proc'::regclass,
  'sde.st_envelope_in(cstring)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

insert into pg_depend (classid, objid, objsubid,
                       refclassid, refobjid, refobjsubid, deptype) values (
  'pg_proc'::regclass,
  'sde.st_envelope_out(sde.st_envelope)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

I suppose the evidence about what happened to those rows is long gone, so there's not much point in doing anything but
patchingthings up to the point where you can run pg_upgrade. 

            regards, tom lane

Well, happy to report pg_upgrade worked, and have 9.5 cluster up and running.

Will be testing during weekend but everything looks ok. Just had to tweak a few settings that changed from 9.3 to 9.5
(logsand checkpoint_segments). 

Life's good.

Thanks again, and just have to say it - open source rocks!

Duarte

-----Mensagem original-----
De: Duarte Carreira
Enviada: 25 de janeiro de 2019 18:24
Para: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org
Assunto: RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Tom, can't thank you enough.

Now, the 2nd query and results are like so:
select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where refobjid = 'sde.st_envelope'::regtype;
"functionst_envelope_in(cstring)";"n" 
"function st_envelope_out(st_envelope)";"n"
"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"

I'll try pg_upgrade again and let you know how it goes. (fingers crossed)

Thanks,
Duarte

-----Mensagem original-----
De: Tom Lane <tgl@sss.pgh.pa.us>
Enviada: 25 de janeiro de 2019 18:19
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:
> 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"

OK, that looks about like what I'd expect.

> 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"

Ah-hah --- st_envelope_in and st_envelope_out are not mentioned here?
That explains your problem.  You'd need to add those two rows to pg_depend, which could go something like

insert into pg_depend (classid, objid, objsubid,
                       refclassid, refobjid, refobjsubid, deptype) values (
  'pg_proc'::regclass,
  'sde.st_envelope_in(cstring)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

insert into pg_depend (classid, objid, objsubid,
                       refclassid, refobjid, refobjsubid, deptype) values (
  'pg_proc'::regclass,
  'sde.st_envelope_out(sde.st_envelope)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

I suppose the evidence about what happened to those rows is long gone, so there's not much point in doing anything but
patchingthings up to the point where you can run pg_upgrade. 

            regards, tom lane