hibernate could not get next sequence value

Question

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.

score 96 · Accepted Answer · edited Nov 23 '14 at 15:21

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.

Are you willing to help me with a similar problem? Here is the link: http://stackoverflow.com/questions/25252541/generatedvalue-for-a-java-abstract-superclass-over-mysql — CodeMed, Aug 11 '14 at 21:51
@Craig Ringer I have had solved the problem by adding appocationSize = 1, but is it a proper way ? — Tomasz Waszczyk, Mar 03 '15 at 18:06
The `@GeneratedValue(strategy = GenerationType.IDENTITY)` worked for me with Spring Data JPA, PostgreSQL 9.5, and v 9.4 of the JDBC driver. — Kent Bull, Feb 29 '16 at 20:19
@TomaszWaszczyk-PantaRhei Yep, `allocationSize = 1` is typical if you're creating the sequences from PostgreSQL, since PostgreSQL expects them to advance one at a time but most JPA implementations assume they'll advance in blocks of 50. — Craig Ringer, Mar 01 '16 at 02:08

score 46 · Answer 2 · edited Jan 06 '15 at 14:01

46

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

edited Jan 06 '15 at 14:01

Matt

17,290
7
57
71

answered May 17 '12 at 00:05

davidg

695
6
8

Thanks man, I haven't use strategy property that's why getting error. – user3145373 ツ Mar 28 '14 at 08:20

score 14 · Answer 3 · answered Oct 01 '15 at 00:54

14

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;

answered Oct 01 '15 at 00:54

virag

562
6
9

2

yes, this approach works fine (espesially if it's a pet-project), but it's bad practice to use it from performance point of view (in high load applications). Hibernate needs to define primary key value for each Entity. To retreive it in case of GenerationType.IDENTITY , Hibernate has to do the insert statement immediately. This approach doesn't allow Hibernate perfom it's optimization (like collecting all changes together and make them in single query - JDBC batch). So better solution is GenerationType.SEQUENCE – Workin_Man Jun 02 '18 at 18:05

score 11 · Answer 4 · answered Oct 11 '13 at 09:09

11

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 ~

answered Oct 11 '13 at 09:09

splatter_fadli

761
4
16
32

this doesn't really solve the problem if we have multiple sequences that needs to be used – aggietech Nov 25 '14 at 15:40
1

at least for Postgres this is "NO CYCLE" and not "NOCYCLE" – Felix Dobslaw Jul 25 '17 at 20:34

score 2 · Answer 5 · answered Mar 08 '13 at 04:18

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

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.
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.
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!

FWIW, mixed case is fine. You just have to "Double Quote" everything to preserve case. This is actually SQL standard behaviour, MySQL just ignores it. — Craig Ringer, Jan 23 '15 at 20:33

score 2 · Answer 6 · answered Jun 19 '13 at 16:01

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.

score 1 · Answer 7 · answered Jun 28 '18 at 21:41

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;

score 0 · Answer 8 · answered Jan 02 '18 at 12:17

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.

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

It worked for me after making the above change.

score 0 · Answer 9 · answered May 28 '19 at 11:34

0

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

answered May 28 '19 at 11:34

Rajdeep

270
2
11

score 0 · Answer 10 · answered Sep 23 '19 at 17:52

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")

score 0 · Answer 11 · answered Nov 04 '19 at 21:31

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");

hibernate could not get next sequence value

11 Answers11

Linked