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

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