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

User · Answer

If using Postgres  create sequence manually with name  hibernate sequence   It will work

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For anyone using FluentNHibernate  my version is 2 1 2   it s just as repetitive but this works   public class UserMap   ClassMap lt User gt        public UserMap                 Table  users            Id x   gt  x Id  Column  id   GeneratedBy SequenceIdentity  users id seq

User · Answer

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

User · Answer

I seem to recall having to use  GeneratedValue strategy   GenerationType IDENTITY  to get Hibernate to use  serial  columns on PostgreSQL

User · Answer

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

[hibernate] hibernate could not get next sequence value

Examples related to hibernate

Examples related to postgresql