postgresql 休眠无法获得下一个序列值

hibernate could not get next sequence value

提问by user468587

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:

我有 gwt 应用程序连接到后端的 postgres DB，还有一个 Java 类“Judgement”映射 DB 中的表“judgements”，当我尝试将判断持久化到 db 时，它抛出了以下错误：

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

我在数据库中有一个序列名称“judgements_id_seq”

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

谁能告诉我怎么了？？？谢谢。

回答by Craig Ringer

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.

Hibernate 的 PostgreSQL 方言不是很清楚。它不知道您的每个 SERIAL 序列，并且假设有一个名为“hibernate_sequence”的全局数据库范围的序列可以使用。

---

---

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

（更新：似乎较新的 Hibernate 版本可能会在GenerationType.IDENTITY指定时使用默认的每表序列。测试您的版本，如果它适合您，请使用它而不是下面的。）

---

---

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=1is quite important. If you omit it, Hibernate will blindly assume that the sequence is defined with INCREMENT 50so when it gets a value from a sequence it can use that value and the 49 values below itas 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.

将allocationSize=1是非常重要的。如果你省略它，Hibernate 会盲目地假设序列是用定义的，INCREMENT 50所以当它从序列中获取一个值时，它可以使用该值和它下面的 49 个值作为唯一的生成键。如果您的数据库序列增加 1 - 默认值 - 那么这将导致独特的违规，因为 Hibernate 尝试重新使用现有的键。

Note that getting one key at a time willresult in an additional round trip per insert. As far as I can tell Hibernate isn't capable of using INSERT ... RETURNINGto 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 nextvalto get the value then insertthat 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 andthe underlying database sequence. That'll cause Hibernate to call nextvalless frequently and cache blocks of keys to hand out as it goes.

请注意，一次获取一个密钥将导致每个插入的额外往返。据我所知，Hibernate 不能INSERT ... RETURNING用来有效地返回生成的密钥，它显然也不能使用 JDBC 生成的密钥接口。如果你告诉它使用一个序列，它会调用nextval然后insert明确地获取值，从而导致两次往返。为了降低成本，您可以在具有大量插入的键序列上设置更大的增量，记住在映射和底层数据库序列上设置它。这将导致 Hibernatenextval不那么频繁地调用并缓存密钥块，以便在进行时分发。

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 getGeneratedKeysor using INSERT ... RETURNINGwith DEFAULTfor 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.

我相信你可以从上面看到我不同意这里所做的 Hibernate 设计选择，至少从将它与 PostgreSQL 一起使用的角度来看。他们应该使用getGeneratedKeys或使用INSERT ... RETURNINGwithDEFAULT作为键，让数据库处理这个，而 Hibernate 不必为序列的名称或对它们的显式访问而烦恼。

BTW, if you're using Hibernate with Pg you'll possibly also want an oplock trigger for Pgto 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.

顺便说一句，如果您将 Hibernate 与 Pg 一起使用，您可能还需要Pg 的 oplock 触发器，以允许 Hibernate 的乐观锁定与普通数据库锁定安全交互。如果没有它或类似的东西，您的 Hibernate 更新将倾向于破坏通过其他常规 SQL 客户端所做的更改。问我怎么知道。

回答by davidg

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

我似乎记得不得不使用@GeneratedValue(strategy = GenerationType.IDENTITY)让 Hibernate 在 PostgreSQL 上使用“串行”列。

回答by virag

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

您需要使用策略 GenerationType.IDENTITY 而不是 GenerationType.AUTO 设置您的 @GeneratedId 列

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

回答by splatter_fadli

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

我之前遇到过同样的错误，请在您的数据库中键入此查询 CREATE SEQUENCE hibernate_sequence START WITH 1 INCREMENT BY 1 NOCYCLE;

that's work for me, good luck ~

这对我有用，祝你好运~

回答by RickB

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

我还想添加一些关于 MySQL 到 PostgreSQL 迁移的说明：

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.

1. 在您的 DDL 中，在对象命名中更喜欢使用“_”（下划线）字符进行单词分隔，而不是驼峰式大小写约定。后者在 MySQL 中运行良好，但在 PostgreSQL 中带来了很多问题。
2. 模型类标识字段中 @GeneratedValue 注释的 IDENTITY 策略适用于 hibernate 3.2 及更高版本中的 PostgreSQLDialect。此外，AUTO 策略是 MySQLDialect 的典型设置。
3. 如果您使用 @Table 注释模型类并将文字值设置为等于表名，请确保您确实创建了要存储在公共模式下的表。

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

我现在记得的就这些，希望这些提示可以让您节省几分钟的反复试验！

回答by Thiago C. S Ventura

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.

就我而言，问题是 Postgres 中序列的所有者。因此，如果上述任何解决方案没有解决您的问题，请检查序列的所有者是否是应该具有权限的用户/角色。

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.

我希望它对任何人都有用。

回答by Stephane

Using the GeneratedValueand GenericGeneratorwith the nativestrategy:

使用GeneratedValue和GenericGenerator与native策略：

@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_sequenceas Hibernate looks up for such a sequence by default:

我必须创建一个序列调用，hibernate_sequence因为 Hibernate 默认查找这样的序列：

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

回答by Rajdeep

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

如果使用 Postgres，请手动创建名为“hibernate_sequence”的序列。它会起作用。

回答by SuniiilSingh

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;

请使用以下查询并更改您的表： 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

并在您的实体类中使用 this

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

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

回答by Tahbaza

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

对于任何使用 FluentNHibernate（我的版本是 2.1.2）的人来说，它都是重复的，但这是有效的：

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