среда, 21 сентября 2011 г.

PostgreSQL 9.1 create extension and database upgrade

Yesterday I was eager to try some new 9.1 features, in particular to experiment with exclude constraints (I've read they were improved since 9.0). So, I decided to upgrade my test 9.0 installation. It worth mentioning that it was 8.4 installation, previously upgraded to 9.0.
Everything went as expected, even my C functions were successfully recompiled without any warnings.
So, I decided to try extensions. I had hstore module installed in my db, so I issued "create extension" and received the following error:


db=# CREATE EXTENSION hstore WITH SCHEMA public FROM unpackaged;
ERROR: function hstore_recv(internal) does not exist


I looked at hstore definition in /usr/local/share/postgresql/extension/hstore--1.0.sql, copied definition of this function, created it, but received error saying that some other function doesn't exist.
So I run full script to create unpackaged extension:

# sed -e 's|MODULE_PATHNAME|$libdir/hstore|' < /usr/local/share/postgresql/extension/hstore--1.0.sql | su pgsql -c 'psql -d db '

After that it was possible to create extension:

db=# CREATE EXTENSION hstore WITH SCHEMA public FROM unpackaged;


It seems that the reason for this behavior is that I didn't upgrade hstore module after upgrading database from 8.4 to 9.0...

Комментариев нет:

Отправить комментарий