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

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

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&gt; [image:
Twitter]<http://www.twitter.com/garyharan&gt; [image:
LinkedIn] <http://ca.linkedin.com/in/garyharan&gt;

On Thu, Jul 5, 2012 at 2:56 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 07/05/2012 09:06 PM, Gary Haran wrote:

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

If you upgrade an existing database to 9.1 and that database has contrib
modules loaded in it, you must convert them to extensions using the
syntax I gave previously. It's right there in the release notes under
"Migrating to version 9.1":

http://www.postgresql.org/docs/9.1/static/release-9-1.html#AEN107367

This is a bit of a wart, in that it'd be much nicer to be able to
transparently re-package pre-extension contrib modules into extensions.
I know Dimitri and Tom tried to get this working, but it clearly wasn't
possible to make it work simply and reliably. Since it's a one-off
migration during upgrade, it isn't that big a deal.

If you need help doing this or you have further questions, please ask
for help on the pgsql-general mailing list.

--
Craig Ringer

On Thu, Jul 5, 2012 at 1:56 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 07/05/2012 02:05 AM, gary.haran@gmail.com wrote:

development=# create extension 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.

what about adding a HINT there? something like "you probably need to
use CREATE EXTENSION ... FROM unpackaged"

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

On 07/05/2012 10:18 PM, Jaime Casanova wrote:

On Thu, Jul 5, 2012 at 1:56 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 07/05/2012 02:05 AM, gary.haran@gmail.com wrote:

development=# create extension 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.

what about adding a HINT there? something like "you probably need to
use CREATE EXTENSION ... FROM unpackaged"

Yeah, I suggested that. It's not trivial to make sure that this is
really because of the extension and not because the user just happened
to create their own type called "hstore" (or whatever the
type(s)/function(s) the ext provides) are, but I guess with a hint you
don't have to. I'm assuming if it were simple it would've been done as
part of extension implementation. It's certainly way beyond me.

--
Craig Ringer