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
debuggingand placed the below SQL before and after the hstore creation in the file produced by the pg_dump and
determinedthat these operator objects only become present immediately after the creation of the hstore extension, and
notbefore. Then, much later down the pg_dump file, it attempts to create the operator family for these 4 items
producingthe errors. I did a pg_dump of the same database on a 9.1 instance and it does not produce the operator
creationobjects SQL outside of the extension. This seems to be something that happened as part of the pg_upgrade. Any
ideawhy 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@sss.pgh.pa.us]
Sent: Wednesday, April 06, 2016 7:01 PM
To: Feld, Michael (IMS) <FeldM@imsweb.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" <FeldM@imsweb.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,
youhad an "unpackaged" 
(that is, pre-extensions) version of hstore lying about.  But then you'd probably get conflicts on all the
hstore-relatedobjects, 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
becauseyou'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
notthe addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or
copyingof this communication is strictly prohibited. If you have received this e-mail in error, please notify the
senderof the error.