Re: BUG #6718: Cannot delete, create or check existence of extension

On 07/05/2012 02:05 AM, gary(dot)haran(at)gmail(dot)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