Switching from MySQL to PostgreSQL: SQLGrammarException: could not get next sequence value

[Russell Bateman]

Folks, I have just switched the underlying database from MySQL to PostgreSQL (corporate requirement) and now my reads work fine, but my writes do not. In other words:

public Fun create( Fun fun ) throws HibernateException
{
    Transaction x = null;

    try
    {
        transaction = session.beginTransaction();
        session.save( fun );          <--- throws exception here!
        transaction.commit();
        session.flush();
        ...

...throws

org.hibernate.exception.SQLGrammarException: could not get next sequence value

In the Eclipse debugger, examining HibernateException, I see:

(exception) SQLGrammarException
(cause) PSQLException
(detailMessage) "could not get next sequence value"
(sql) "select nextval( 'hibernate_sequence' )"


Now, I am able to throw anything I try into the database by hand (via psql). There is no problem of privilege. And, as I say, I can read out (via Hibernate Criteria) anything I like and do same using psql. I will confess I have no experience so far in switching Hibernate dialects and this is the first time I've used PostgreSQL.

I have created a very minimal table, Fun, to see if I'm using some columns wrong in the eyes of PostgreSQL, but no matter if I reduce the object to just a single, integer field, I still get this, so it's not about using some bizarre thing like DATE, TIMESTAMP or even some OneToOne, OneToMany, etc. construct incorrectly.

(FYI) hibernate.cfg.xml:

<hibernate-configuration>
    <session-factory name="hibernate1">
        <property name="connection.driver_class">org.postgresql.Driver</property>
        ...
        <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
        ...

(used to be)

<hibernate-configuration>  
    <session-factory name="hibernate1">  
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        ...  
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

I thought there wouldn't be too much to do beyond this configuration switcheroo. I'd be grateful if anyone could point me somewhere to go.

Much thanks,

Russ Bateman

[chris webster]

No idea about Hibernate, but have you checked that the SQL to fetch the sequence value actually works in your SQL shell, in case there's some problem with the sequence definition/grants?

select nextval( 'hibernate_sequence' );

[Bogdan Baraila]

If you use "native" as your objects id generation class then you need a sequence in the database that will generate your ids. By default hibernate is looking for a sequence named "hibernate_sequence". If you don't have it then you can generate it using a query like this:

CREATE SEQUENCE hibernate_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 5;

[Russell Bateman]

Profuse thanks. Both these responses were useful to me and it all makes complete sense now. I had only direct experience in using Hibernate atop MySQL, apparently an easier thing to do. Thanks again, friends!