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

Duarte Carreira <DCarreira(at)edia(dot)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