Re: pg_upgrade error regarding hstore operator

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

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