Wolfmans Howlings

A programmers Blog about Programming solutions and a few other issues

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?

Posted in Grails  |  Tags grails,hibernate,postgresql  |  11 comments

Comments

  1. Fletch said on Thu Nov 12 00:31:08 -0800 2009
    Thanks for this. Detailed instructions. Sounds great and I'll look to try it out in due course.
  2. seanoc5 said on Wed Nov 18 15:14:51 -0800 2009
    Ah, Wolfman, this is good. I thought I was out in the crazy wilderness trying to use postgresql with grails (or hibernate, or most other popular frameworks). This certainly helps me.

    I still have some general strategy/performance questions, but will try my luck on the grails mailing list.

    Many thanks for writing this up, well worth the read.
    +1

    Sean
  3. Chris said on Mon Nov 23 10:06:48 -0800 2009
    Wow - this is awesome. So, my main question is: how did you know to do this? I'm new to Java, and wouldn't even know where to start to do this myself. Do you have previous Hibernate experience, or were you just having an exceptionally clever day when you came up with this?

    Thanks tons for posting this. :)
  4. wolfman said on Mon Nov 23 14:59:19 -0800 2009
    @Chris - I just started using Grails/Gorm/Hibernate, but I have been using JDBC and Spring for quite a while. I knew about the problem with Postgresql for a while, and have incorporated the RETURNING id in all my Spring DAOs for a while. I just Googled my ass off to find out how to do it for Hibernate, as I could not believe it was simply broken and unfixable.

    I have to say that it probably took me 4 hours to track down the information on how to do it, and about 10 minutes to actually implement it.
  5. Chris said on Tue Nov 24 10:06:55 -0800 2009
    @wolfman - thanks! I like Grails, but I'm always feeling like I'm missing something by not having Spring/Hibernate experience to start. LOL - that's the story of writing code, isn't it? Spending 90% of the time to figure out the solution, and 10% on the implementation. :)
  6. Gustavo said on Fri Nov 12 04:17:50 -0800 2010
    Hi wolfman,

    I've tested this with grails 1.3.5, apperently PostgreSQL82Dialect is being picked up by grails, but it uses the default hibernate approach for PSQL with identity (insert into + currval)

    My guess is that something has changed on hibernate, and some config is missing. As I'm not experiencied with hibernate, I'm clueless.

    Have you any hints on what might be, or where may I look into?
    Thanks
  7. wolfman said on Fri Nov 12 11:39:19 -0800 2010
    I haven't tried it with the latest Grails, but I'll do so.

    Did you put

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

    in GRAILS_PROJECT/grails-app/conf/Config.groovy
  8. Gustavo said on Fri Nov 12 12:25:13 -0800 2010
    Hi there, I just solved it.
    Apperently latest postgre driver (8.4-702jdbc4) does not support this. Downgraded to 8.3-606jdbc4 and things got back to work!!

    Thanks for your help!!!
  9. Fletch said on Tue Feb 22 07:49:13 -0800 2011
    Tried this with postgresql-9.0-801.jdbc4 and also found that it didn't work but got it going with the 8.3 version mentioned by Gustavo. I wonder why they have removed this functionality from the JDBC driver. Does anyone have any idea?

    By the way I would be interested in how the efficiency compares with doing the same insert with no return ID and not selecting the ID afterwards (i.e. when you don't actually need to access the object after a save).
  10. Fletch said on Tue Feb 22 07:52:22 -0800 2011
    Coding questions... Wouldn't it be more appropriate to name the class PostgresReturningIdDialect?

    What is the function of the empty constructor?
  11. Jim Morris said on Tue Feb 22 10:49:40 -0800 2011
    @Fletch
    It can be named anything you want, I just followed what others had done with naming.

    Not sure why I put in an empty ctor, probably habit as it has a base class.

    I don't use postgresql 9 yet but I'll look into it when I do need it.

(leave email »)