Re: pg_upgrade error regarding hstore operator

Thanks for the reply Tom. template1 is definitely empty and does not contain any hstore objects. I did a little debugging and placed the below SQL before and after the hstore creation in the file produced by the pg_dump and determined that these operator objects only become present immediately after the creation of the hstore extension, and not before. Then, much later down the pg_dump file, it attempts to create the operator family for these 4 items producing the errors. I did a pg_dump of the same database on a 9.1 instance and it does not produce the operator creation objects SQL outside of the extension. This seems to be something that happened as part of the pg_upgrade. Any idea why these have showed up outside the extension? Is there anything I can do to fix this? Thanks for all of your help.

Mike

SELECT am.amname AS index_method,
opf.opfname AS opfamily_name,
amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid AND
amop.amopfamily = opf.oid and opf.opfname like '%hstore%'
ORDER BY index_method, opfamily_name, opfamily_operator;

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, April 06, 2016 7:01 PM
To: Feld, Michael (IMS) <FeldM(at)imsweb(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" <FeldM(at)imsweb(dot)com> writes:
> Thanks for the assist Tom. That worked for us. Noticing a different
> issue following the pg_upgrade. If we take a pg_dump of a database on
> this upgraded instance with the hstore extension and try to pg_restore
> it back up to the same instance we get the following errors

Those are the *only* errors you get? That seems rather odd. I could believe something like this happening if, say, you had an "unpackaged"
(that is, pre-extensions) version of hstore lying about. But then you'd probably get conflicts on all the hstore-related objects, not only the opclasses.

In any case, by far the most likely explanation is that you're trying to restore into a non-empty database, probably because you've put stuff into
template1 and are cloning the new database from there.

regards, tom lane

________________________________

Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.