"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