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

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 (logs and 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(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)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; "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(at)sss(dot)pgh(dot)pa(dot)us>
Enviada: 25 de janeiro de 2019 18:19
Para: Duarte Carreira <DCarreira(at)edia(dot)pt>
Cc: pgsql-general(at)postgresql(dot)org
Assunto: 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