Re: pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" <FeldM(at)imsweb(dot)com> writes:
> 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.

I got some time today to try to reproduce this problem, and failed.
What I did was:
* install hstore 1.0 extension in a 9.1 database
* upgrade the extension to 1.1 (as per instructions upthread)
* pg_upgrade the database to HEAD
* pg_dump the database

The pg_dump output shows the expected CREATE EXTENSION command and
no other extraneous objects. So the procedure is fine. I have to
conclude there was something weird about the initial state of your
9.1 database. I have too little info to say what exactly.

> 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;

Uh, what did you get from that query? Might also be useful to see

select * from pg_opclass where opcintype = 'hstore'::regtype;

and the output of "\dx+ hstore" in psql.

regards, tom lane