postgresql hstore in rails

I followed the railscasts on hstore but i needed some additional steps to have it working on my environment.

I will describe them in this post.

The railscast 342 hstore - pro give very good instructions to use it(like always)to use hstore attribute in models.

Reading the railscast is a must.

installation

It is necessary to install the postgresql contrib package to use hstore extension.

On ubuntu, we install the following package :

sudo aptitude install postgresql-contrib-9.1

roles

Find below the default privilege, i grant to my rails users :

postgres=# \du
                               List of roles
   Role name   |                   Attributes                   | Member of
---------------+------------------------------------------------+-----------
 aix_inventory | Create DB                                      | {}
 djouxblog     | Create DB                                      | {}
 postgres      | Superuser, Create role, Create DB, Replication | {}

The problem is the “Create DB” role is not enough to permit the creation of hstore extension. The user need to have “Superuser” privilege for that.

It’s possible to grant superuser privilege to aix_inventory user here.

postgres=# ALTER ROLE aix_inventory SUPERUSER;
ALTER ROLE
postgres=# \du
                               List of roles
   Role name   |                   Attributes                   | Member of
---------------+------------------------------------------------+-----------
 aix_inventory | Superuser, Create DB                           | {}
 djouxblog     | Create DB                                      | {}
 postgres      | Superuser, Create role, Create DB, Replication | {}

It’s not a good idea to grant permanently this privilege : It’s possible to remove it after migration :

ALTER ROLE aix_inventory NOSUPERUSER;

Another oslution is to create the extension under postgres user :

psql ainv_development -c 'create extension hstore;

after the modification, the migration work correctly(mainly because nothing happens ;) ).

aix_inventory$ rake db:migrate
==  SetupHstore: migrating ====================================================
-- execute("CREATE EXTENSION IF NOT EXISTS hstore")
NOTICE:  extension "hstore" already exists, skipping
   -> 0.0006s
==  SetupHstore: migrated (0.0009s) ===========================================

Adding a hstore attribute

class AddPropertiesToServers < ActiveRecord::Migration
  def up
    add_column :servers, :properties, :hstore
    execute "CREATE INDEX servers_properties ON servers USING GIN(properties)"
  end

  def down
    execute "DROP INDEX servers_properties"
  end
end

But i had this error message when trying to save a object :

ActiveRecord::StatementInvalid: PG::InternalError: ERROR:  Syntax error near ':' at position 4

To correct the problem we need to add a line with serialize in Server model class :

class Server < ActiveRecord::Base
  serialize :properties, ActiveRecord::Coders::Hstore

conclusion

I like hstore attribute. It allow me to develop my schema smoothly. It was not so hard to implement. I like PostgreSQL :)