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

I installed it using an hstore.sql file that comes with the home brew (
https://github.com/mxcl/homebrew/) version of postgresql on OS X. Maybe we
missed a step or something in the whole installation process.

We most likely upgraded our pg version at some point (we try to stay with
the times).

Thanks for your help!

Gary Haran
Phone: 514.909.4947
[image: Facebook] <http://www.facebook.com/gary.haran> [image:
Twitter]<http://www.twitter.com/garyharan> [image:
LinkedIn] <http://ca.linkedin.com/in/garyharan>

On Thu, Jul 5, 2012 at 2:56 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:

> 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<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
>