How to setup django-hstore with an existing app managed by south?

Question

I tried to use django-hstore using this nice tutorial. I added two classes to an existing app managed by South:

class Attribute(models.Model):
    name  = models.CharField(max_length=200, verbose_name=_("name"))
    description = models.CharField(max_length=1000, verbose_name=_("description"))

class Measure(models.Model):
    attribute = models.ForeignKey(Attribute)
    data = hstore.DictionaryField(db_index=True)
    objects = hstore.HStoreManager()

made a schemamigration --auto, launched the migration and got a django.db.utils.DatabaseError: type "hstore" does not exist.

Okay, the tuto seemed to be incomplete, the django-hstore documentation told me to use the custom database backend, i added the following to my settings file:

DATABASES['default']['ENGINE'] = 'django_hstore.postgresql_psycopg2'

Then I got a KeyError: 'default' in south/db/__init__.py", line 78. At this point, the intertubes + some trial/errors pointed me to the SOUTH_DATABASE_ADAPTERS settings variable and I added the following to the settings:

SOUTH_DATABASE_ADAPTERS = {'default': 'south.db.postgresql_psycopg2'}

New error:

File ".../psycopg2/extras.py", line 769, in register_hstore
"hstore type not found in the database. "
psycopg2.ProgrammingError: hstore type not found in the database. please install it from your 'contrib/hstore.sql' file

Now this is odd because I installed the hstore extension :

$ sudo -u postgres psql
create extension hstore;
postgres=# CREATE EXTENSION hstore;
ERROR:  extension "hstore" already exists
postgres=# \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.0     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=# SELECT 'hstore'::regtype::oid;
  oid  
-------
 57704
(1 row)

How is this supposed to work ? I'm using Django 1.4, Postgresql 9.1.

Gerrit

Answer 1

I eventually found that the hstore extension wasn't installed for the specific database I was using:

$ psql -d mydb
psql (9.1.4)
Type "help" for help.

mydb=# SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore';
 oid | typarray 
-----+----------
(0 rows)

mydb=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

mydb=# create extension hstore;
WARNING:  => is deprecated as an operator name
DETAIL:  This name may be disallowed altogether in future versions of PostgreSQL.
CREATE EXTENSION
mydb=# \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.0     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

mydb=# SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore';
  oid  | typarray 
-------+----------
 58800 |    58805
(1 row)

I thought that a database created after the hstore installation would include the extension. Doesn't seem to be the case, am I misinterpreting how extensions work ? Are they database-specific ?

Gerrit

Extensions are definitely database-specific in PostgreSQL. – Dilution

how can i write a script for the same? – Hiramhirasuna

Answer 2

Django now includes a migration operation to create the hstore extension in PostgreSQL:

from django.contrib.postgres.operations import HStoreExtension

class Migration(migrations.Migration):
    ...

    operations = [
        HStoreExtension(),
        ...
    ]

Arlon

Answer 3

Since my last answer, Django deprecated and removed pre_syncdb signal. I've updated the answer to accommodate more recent versions. The basic mechanics are identical for newer versions as both methods rely on signals and the SQL code that only executes if HSTORE extension does not exist.

Django 1.8+

Since Django introduced DB migrations, pre_syncdb signals were marked deprecated in 1.7 and completely removed in 1.9. However, they introduced a new signal called pre_migrate which can be used the same way.

Example:

"""
This is an example models.py which contains all model definition.
"""
from django.db import connection, models
from django.db.models.signals import pre_migrate
from django.dispatch import receiver
import sys

# sender is optional but will be called for every pre_migrate signal if removed
@receiver(pre_migrate, sender=sys.modules[__name__])
def setup_postgres_hstore(sender, **kwargs):
    """
    Always create PostgreSQL HSTORE extension if it doesn't already exist
    on the database before syncing the database.
    Requires PostgreSQL 9.1 or newer.
    """
    cursor = connection.cursor()
    cursor.execute("CREATE EXTENSION IF NOT EXISTS hstore")

# ...rest of your model definition goes here
class Foo(models.Model):
    # ...field definitions, etc.

---

Django 1.6+ (original answer)

One way to ensure that HSTORE extension gets installed during ./manage.py syncdb is to utilize pre_syncdb signals in your models.py file that was introduced with Django 1.6.

Example:

"""
This is an example models.py which contains all model definition.
"""
from django.db import connection, models
from django.db.models.signals import pre_syncdb
from django.dispatch import receiver
import sys

# sender is optional but will be called for every pre_syncdb signal if removed
@receiver(pre_syncdb, sender=sys.modules[__name__])
def setup_postgres_hstore(sender, **kwargs):
    """
    Always create PostgreSQL HSTORE extension if it doesn't already exist
    on the database before syncing the database.
    Requires PostgreSQL 9.1 or newer.
    """
    cursor = connection.cursor()
    cursor.execute("CREATE EXTENSION IF NOT EXISTS hstore")

# ...rest of your model definition goes here
class Foo(models.Model):
    # ...field definitions, etc.

I find that this is useful if you don't want to run it for every new database instance. This method also works for Django unit tests during test database setup.

More info on signal hooks in Django: https://docs.djangoproject.com/en/1.6/ref/signals/#management-signals

Apocynaceous

pre_syncdb is not present in django-1.5.5 I guess. – Shunt

Unfortunately, Django 1.5 only had post_syncdb. Edited my answer with a disclaimer. – Apocynaceous