Wolfmans Howlings

Using Postgresql with Grails

Posted by Jim Morris on Wed Nov 11 13:59:07 -0800 2009

I started playing with Grails, and I am planning to rewrite the blog engine I use for this blog in Grails. As noted earlier I wrote it in Merb originally. Although Merb is nice and lite it seems to be having difficulty keeping up with the gems it is dependent on. A case in point is Cucumber which it says is its recommended way of doing integration tests. The current version of Cucumber is very difficult to get working with Merb. See the Git source to see how I finally did it.

While I was learning Grails, I hooked up Postgresql 8.2 to Grails using the Datasource.groovy file. I turned on SQL logging, and to my horror saw how it was handling the id's. I have been using Postgresql for quite a while now, and used to run into problems with the creation and retrieval of the automatically generated IDs. It seems a lot of people run into these hard to describe problems, as it appears to happen rarely and is probably a race condition. With version >= 8.2 Postgresql introduced a way of inserting a new record and returning the automatically generated id in one atomic statement. This solves all the problems and is more efficient as it does not require two queries for each insert.

insert into users (version, crypted_password, admin, name, date_created, salt)
values (?, ?, ?, ?, ?, ?) RETURNING id

For id when it has this schema

id | bigint | not null default nextval('users_id_seq'::regclass)

The RETURNING id is the trick here.

By default Hibernate, which is the underlying ORM used by GORM, which is the Grails ORM, seems to use a sequence to generate the next id then write the id in the INSERT SQL statement.

select nextval ('hibernate_sequence')

insert into users (version, crypted_password, admin, name, date_created, salt, id)
values (?, ?, ?, ?, ?, ?, ?)

This sequence should work in most cases, as nextval is supposed to be atomic and should return a unique sequence regardless of how many threads are concurrently accessing the database, however I have read that this causes problems in Hibernate and in Grails a bug was filed saying that this causes problems in Grails if you are doing inserts during the bootstrap process, something to do with the way Hibernate caches or batches stuff.

If you force hibernate to use the identity method of sequences id generator:'identity' which is the preferred method anyway, then Hibernate uses this sequence to get the id after an insert...

insert into users (version, crypted_password, admin, name, date_created, salt)
values (?, ?, ?, ?, ?, ?)

select currval('users_id_seq')

Now I know that this sequence (other than being inefficient) can be subject to a race condition where the id returned is not the id that was just generated. I do not know why as Postgresql says it should work, but I have actually been bitten by that on occasion in production, and it was solved by using RETURNING id.

So I was surprised that the current version of Hibernate does not use the currently recommended way of doing this common sequence. However it does provide a way to work around the problem. This solution really only works in Grails, because you have to hard code the column used for the id, if the API in Hibernate was a little more flexible it could be made generic, but that is an issue for the Hibernate JIRA.

Anyway here is the solution for Grails.

First stick this file in GRAILS_PROJECT/src/java/com/e4net/hibernate/dialect/PostgreSQL82Dialect.java

package com.e4net.hibernate.dialect;

import org.hibernate.dialect.PostgreSQLDialect;

public class PostgreSQL82Dialect extends PostgreSQLDialect {

    public PostgreSQL82Dialect() {

    }

    public boolean supportsInsertSelectIdentity() {
            return true;
    }

    public String appendIdentitySelectToInsert(String insertString) {
            return insertString + " RETURNING id";
    }
}

This sets up a extension to the standard Postgresql dialect that Hibernate uses. It tells Hibernate that the id can be returned in the same SQL statement of the INSERT, and also how to modify the insert SQL statement to do that. This is where id is hard coded, if we were passed a little more information like what the identity column was we could make this generic.

Then in your GRAILS_PROJECT/grails-app/conf/DataSource.groovy

            dataSource {
                    dbCreate = "update" // one of 'create', 'create-drop','update'
                    url = "jdbc:postgresql://localhost/wolfmanblog_dev"
                    driverClassName = "org.postgresql.Driver"
                    dialect = 'com.e4net.hibernate.dialect.PostgreSQL82Dialect'
                    logSql = true
                    username = "xxxx"
                    password = "xxxx"
            }

Note the dialect = 'com.e4net.hibernate.dialect.PostgreSQL82Dialect' this tells it to use the class we created above.

Now we need to tell Hibernate to always use the identity method for generating ids. This can be done in the static mapping stanza in each of your domain classes...

static mapping = {
    id generator: 'identity'
}

Or if you are using Grails 1.2-M4 or better you can put this in GRAILS_PROJECT/grails-app/conf/Config.groovy

grails.gorm.default.mapping = {
    id generator:'identity'
}

Which will change the default for all your domain classes.

Now when you do an insert you will get this...

insert into users (version, crypted_password, admin, name, date_created, salt)
values (?, ?, ?, ?, ?, ?) RETURNING id

Which is more efficient and correct.

I'd like to see this in the default Hibernate Postgresql dialect, maybe it could determine the version of Postgresql it is using and do the correct thing?