Hello,
We a little while ago upgraded our databases from 8.4 to 9.1. We
upgraded using pg_upgrade but didn't do anything special for
extensions (other than that the server had the contrib rpm installed).
Everything works just fine but recently we noticed that a lot of the
functions from hstore are no longer available. Interestingly enough
the type is still there as are the columns of that type and the basic
operators also still work:
proddb=> select hstore 'a => b' -> 'a';
?column?
----------
b
(1 row)
However:
proddb=> select hstore(text 'a', text 'b');
ERROR: function hstore(text, text) does not exist
LINE 1: select hstore(text 'a', text 'b');
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
More reading of the docs revealed this:
E.7.2.7. Contrib
All contrib modules are now installed with CREATE EXTENSION rather
than by manually invoking their SQL scripts (Dimitri Fontaine, Tom
Lane)
To update an existing database containing the 9.0 version of a
contrib module, use CREATE EXTENSION ... FROM unpackaged to wrap the
existing contrib module's objects into an extension. When updating
from a pre-9.0 version, drop the contrib module's objects using its
old uninstall script, then use CREATE EXTENSION.
It is unclear what to do. Clearly 8.4 < 9.0 but if we were to run the
old uninstall script this would DROP all columns of hstore type with
disastrous consequences for us. I would argue that the above comment
should warn about that!
What is the official guide line?
Thanks,
Bene
