CODEWITHSUNDEEP
hibernatepostgresql
user468587
user468587

Reputation: 5031

hibernate could not get next sequence value

i have gwt application connect to postgres DB at the backend, and a java class 'Judgement' mapping the table 'judgements' in DB, when i tried to persistent a judgement into db, it threw the following errors:

Caused by: org.hibernate.exception.SQLGrammarException: could not get next sequence value
...
Caused by: org.postgresql.util.PSQLException: ERROR: relation "hibernate_sequence" does not exist

my Judgement class looks like this

@Entity
@Table(name = "JUDGEMENTS")
public class Judgement implements Serializable, Cloneable {

    private static final long serialVersionUID = -7049957706738879274L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "JUD_ID")
    private Long _judId;
...

and my table judgements is: 

   Column    |            Type             |                        Modifiers                        
-------------+-----------------------------+---------------------------------------------------------
 jud_id      | bigint                      | not null default nextval('judgements_id_seq'::regclass)
 rating      | character varying(255)      | 
 last_update | timestamp without time zone | 
 user_id     | character varying(255)      | 
 id          | integer                     | 
Indexes:
    "judgements_pkey" PRIMARY KEY, btree (jud_id)
Foreign-key constraints:
    "judgements_id_fkey" FOREIGN KEY (id) REFERENCES recommendations(id)
    "judgements_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id)

and i have a SEQUENCE name 'judgements_id_seq' in DB

can anyone tell me what's wrong??? thanks.

Upvotes: 56

Views: 120810

Answers (11)

Tahbaza
Tahbaza

Reputation: 9548

For anyone using FluentNHibernate (my version is 2.1.2), it's just as repetitive but this works:

public class UserMap : ClassMap<User>
{
    public UserMap()
    {
        Table("users");
        Id(x => x.Id).Column("id").GeneratedBy.SequenceIdentity("users_id_seq");

Upvotes: 0

SuniiilSingh
SuniiilSingh

Reputation: 107

Please use the following query and alter your table : CREATE SEQUENCE user_id_seq START 1; ALTER TABLE product.users ALTER COLUMN user_id SET DEFAULT nextval('user_id_seq'); ALTER SEQUENCE users.user_id_seq OWNED BY users.user_id;

and use the this in your entity class

@GeneratedValue(strategy = GenerationType.SEQUENCE,generator="user_id_seq")

Upvotes: 0

Rajdeep
Rajdeep

Reputation: 270

If using Postgres, create sequence manually with name 'hibernate_sequence'. It will work.

Upvotes: 0

Stephane
Stephane

Reputation: 12740

Using the GeneratedValue and GenericGenerator with the native strategy:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_native")
@GenericGenerator(name = "id_native", strategy = "native")
@Column(name = "id", updatable = false, nullable = false)
private Long id;

I had to create a sequence call hibernate_sequence as Hibernate looks up for such a sequence by default:

create sequence hibernate_sequence start with 1 increment by 50;
grant usage, select on all sequences in schema public to my_user_name;

Upvotes: 1

Chaitanya Ankam
Chaitanya Ankam

Reputation: 31

The Database that we are using should be mentioned under search_path in Postgres SQL Configuration file. This can be done by editing Postgressql configuration file by setting search_path along with database name for example: TESTDB.

  1. Find postgressql.conf file under data folder of Postgres SQL datbase.
  2. Set search_path = "$user", public, TESTDB;
  3. Restart the Postgres SQL service to affect the change.

It worked for me after making the above change.

Upvotes: 0

virag
virag

Reputation: 582

You need to set your @GeneratedId column with strategy GenerationType.IDENTITY instead of GenerationType.AUTO

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "JUD_ID")
private Long _judId;

Upvotes: 14

davidg
davidg

Reputation: 695

I seem to recall having to use @GeneratedValue(strategy = GenerationType.IDENTITY) to get Hibernate to use 'serial' columns on PostgreSQL.

Upvotes: 46

Craig Ringer
Craig Ringer

Reputation: 324345

Hibernate's PostgreSQL dialect isn't very bright. It doesn't know about your per-SERIAL sequences, and is assuming there's a global database-wide sequence called "hibernate_sequence" that it can use.

(UPDATE: It appears that newer Hibernate versions may use the default per-table sequences when GenerationType.IDENTITY is specified. Test your version and use this instead of the below if it works for you.)

You need to change your mappings to explicitly specify each sequence. It's annoying, repetitive, and pointless.

@Entity
@Table(name = "JUDGEMENTS")
public class Judgement implements Serializable, Cloneable {

    private static final long serialVersionUID = -7049957706738879274L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="judgements_id_seq")
    @SequenceGenerator(name="judgements_id_seq", sequenceName="judgements_id_seq", allocationSize=1)
    @Column(name = "JUD_ID")
    private Long _judId;
...

The allocationSize=1 is quite important. If you omit it, Hibernate will blindly assume that the sequence is defined with INCREMENT 50 so when it gets a value from a sequence it can use that value and the 49 values below it as unique generated keys. If your database sequences increment by 1 - the default - then this will result in unique violations as Hibernate tries to re-use existing keys.

Note that getting one key at a time will result in an additional round trip per insert. As far as I can tell Hibernate isn't capable of using INSERT ... RETURNING to efficiently return generated keys, nor can it apparently use the JDBC generated keys interface. If you tell it to use a sequence, it'll call nextval to get the value then insert that explicitly, resulting in two round trips. To reduce the cost of that, you can set a greater increment on key sequences with lots of inserts , remembering to set it on the mapping and the underlying database sequence. That'll cause Hibernate to call nextval less frequently and cache blocks of keys to hand out as it goes.

I'm sure you can see from the above that I don't agree with the Hibernate design choices made here, at least from the perspective of using it with PostgreSQL. They should be using getGeneratedKeys or using INSERT ... RETURNING with DEFAULT for the key, letting the database take care of this without Hibernate having to trouble its self over the names of the sequences or explicit access to them.

BTW, if you're using Hibernate with Pg you'll possibly also want an oplock trigger for Pg to allow Hibernate's optimistic locking to interact safely with normal database locking. Without it or something like it your Hibernate updates will tend to clobber changes made via other regular SQL clients. Ask me how I know.

Upvotes: 96

splatter_fadli
splatter_fadli

Reputation: 781

I got same error before, type this query in your database CREATE SEQUENCE hibernate_sequence START WITH 1 INCREMENT BY 1 NOCYCLE;

that's work for me, good luck ~

Upvotes: 11

Thiago C. S Ventura
Thiago C. S Ventura

Reputation: 2592

I think you already have enough answer, but I got exactly the same error and my problem was another one. And I wasted a little bit of time trying to solve it.

In my case the problem was the owner of sequence in Postgres. So, if any solution above did not solved your problem, check if the owner of sequence is the user/role which should have permission.

Follows a sample:

CREATE SEQUENCE seq_abcd
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.seq_abcd OWNER TO USER_APP;

I hope it can be useful for anyone.

Upvotes: 2

RickB
RickB

Reputation: 653

I would also like to add a few notes about a MySQL-to-PostgreSQL migration:

  1. In your DDL, in the object naming prefer the use of '_' (underscore) character for word separation to the camel case convention. The latter works fine in MySQL but brings a lot of issues in PostgreSQL.
  2. The IDENTITY strategy for @GeneratedValue annotation in your model class-identity fields works fine for PostgreSQLDialect in hibernate 3.2 and superior. Also, The AUTO strategy is the typical setting for MySQLDialect.
  3. If you annotate your model classes with @Table and set a literal value to these equal to the table name, make sure you did create the tables to be stored under public schema.

That's as far as I remember now, hope these tips can spare you a few minutes of trial and error fiddling!

Upvotes: 2

Related Questions