On 07/05/2012 02:05 AM, gary.haran@gmail.com wrote:
> development=# create extension hstore;
> ERROR: type "hstore" already exists
> development=# drop extension hstore;
> ERROR: extension "hstore" does not exist
> development=# CREATE EXTENSION IF NOT EXISTS hstore;
> ERROR: type "hstore" already exists
First, thanks for the info in the report.
At a guess, it has the hstore data type in it from before the extension
system exists. You need to follow the upgrade instructions to convert it
to an extension. This isn't a bug in that Pg is working as designed,
though it's certainly not very nice user interface.
See:
http://www.postgresql.org/docs/9.1/static/sql-createextension.html
specifically the "FROM old_version" clause, eg:
CREATE EXTENSION hstore FROM unpackaged;
(I think that's right; I haven't used the extension system in an upgrade).
If you need more help please ask on pgsql-general as this doesn't appear
to be a bug from the information supplied. However, I think "CREATE
EXTENSION" should emit a HINT for already-exists errors, guiding people
to info on how to upgrade the extension.
I'm assuming the database "development" was loaded from a dump from an
older version or was binary-upgraded from an older version. If it was
created anew on 9.1, how did the "hstore" type get loaded?
--
Craig Ringer