Thread: BUG #6718: Cannot delete, create or check existence of extension
The following bug has been logged on the website:
Bug reference: 6718
Logged by: Gary Haran
Email address: gary.haran@gmail.com
PostgreSQL version: 9.1.4
Operating system: OS X
Description:=20=20=20=20=20=20=20=20
development=3D# select version();
version=
=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
--------------------------------------------------------------------
PostgreSQL 9.1.4 on x86_64-apple-darwin11.4.0, compiled by Apple clang
version 3.1 (tags/Apple/clang-318.0.61) (based on LLVM 3.1svn), 64-bit
(1 row)
development=3D# create extension hstore;
ERROR: type "hstore" already exists
development=3D# drop extension hstore;
ERROR: extension "hstore" does not exist
development=3D# CREATE EXTENSION IF NOT EXISTS hstore;
ERROR: type "hstore" already exists
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
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=3D# 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" --=20 Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n
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@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 >> 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 >
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