"Feld, Michael (IMS)" <[email protected]> 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
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
