PostgreSQL串行类型的Hibernate注释

正确使用：

一个序列只适用于<只有要求编号是唯一的。如果你还需要它是单调和有序的，你应该考虑使用带有计数器字段的普通表，通过 UPDATE ... RETURNING 或 SELECT ... FOR UPDATE （Hibernate中的悲观锁定）或通过Hibernate乐观锁定。通过这种方式，您可以保证无间隙增量没有漏洞或无序输入。

改为：

仅为一个计数器创建一个表格。在其中有一行，并在读取它时进行更新。这将锁定它，阻止其他交易在您提交之前获取ID。

因为它会强制所有交易连续运行，请尝试保留生成发票编号的交易

或者，您可以：

• 为invoice_number定义一个实体，添加一个 @Version 列，并让乐观锁定处理冲突;
• 为invoice_number定义一个实体，并在Hibernate中使用显式的悲观锁定来执行select ... for update然后进行更新。

  全部这些选项将序列化您的交易 - 通过使用@Version回滚冲突，或阻止它们（锁定），直到锁持有者提交。无论哪种方式，无缝序列会真正减缓应用程序的面积，因此只有在必要时才使用无间隙序列。

  @ GenerationType.TABLE ：使用 @ GenerationType.TABLE 和 @TableGenerator（initialValue = 1，...）。不幸的是，虽然GenerationType.TABLE允许您通过@TableGenerator指定分配大小，但它不提供有关排序或回滚行为的任何保证。请参阅JPA 2.0规范，第11.1.46节和第11.1.17节。特别是此规范没有定义这些策略的确切行为。和脚注102 可移植应用程序不应该在其他持久字段或属性上使用GeneratedValue注释[ @Id 主键]。因此，使用 @ GenerationType.TABLE 进行编号是不安全的，或编号不在主键属性上，除非您的JPA提供者提供比标准更多的保证。

  如果您遇到序列

  strong>：

  海报指出，他们已经有使用数据库的应用程序，它们已经使用了一个序列，所以他们一直坚持使用它。

  JPA标准不保证您可以使用除@Id之外的生成列，您可以：（a）只要您的提供者允许您忽略并继续，或者（b）插入一个默认值并从数据库重新读取，后者更安​​全：

  @Column（name =inv_seq，insertable = false，updatable = false） public Integer getInvoiceSeq（）{ return invoiceSeq; $ / code>

  因为 insertable = false 提供者不会尝试为列指定一个值。你现在可以在数据库中设置一个合适的 DEFAULT ，比如 nextval（'some_sequence'） 。在持久化之后，您可能需要使用 EntityManager.refresh（）从数据库中重新读取实体 - 我不确定持久性提供程序是否会为您执行此操作，我没有检查过规范或写了一个演示程序。

  唯一的缺点是它似乎不能生成@NotNull或 nullable = false ，因为提供者不了解数据库具有该列的默认值。它仍然可以在数据库中 NOT NULL 。

  如果幸运的话，其他应用程序也会使用从 INSERT 的列列表中省略序列列，或者显式指定关键字 DEFAULT 作为值，而不是调用 nextval 。通过在 postgresql.conf 中启用 log_statement ='all'并搜索日志。如果他们这样做了，那么如果你决定用 DEFAULT 替换为 BEFORE INSERT ... FOR EACH ROW 从计数器表中设置 NEW.invoice_number 的触发函数。

  I have a PostgreSQL table in which I have a column inv_seq declared as serial.

  I have a Hibernate bean class to map the table. All the other columns are read properly except this column. Here is the declaration in the Hibernate bean class:

  .... .... @GeneratedValue(strategy=javax.persistence.GenerationType.AUTO) @Column(name = "inv_seq") public Integer getInvoiceSeq() { return invoiceSeq; } public void setInvoiceSeq(Integer invoiceSeq) { this.invoiceSeq = invoiceSeq; } .... ....

  Is the declaration correct? I am able to see the sequential numbers generated by the column in the database, but I am not able to access them in the java class.

  Please help.

  解决方案

  Danger: Your question implies that you may be making a design mistake - you are trying to use a database sequence for a "business" value that is presented to users, in this case invoice numbers.

  Don't use a sequence if you need to anything more than test the value for equality. It has no order. It has no "distance" from another value. It's just equal, or not equal.

  Rollback: Sequences are not generally appropriate for such uses because changes to sequences are't rolled back with transaction ROLLBACK. See the footers on functions-sequence and CREATE SEQUENCE.

  Rollbacks are expected and normal. They occur due to:

  • deadlocks caused by conflicting update order or other locks between two transactions;
  • optimistic locking rollbacks in Hibernate;
  • transient client errors;
  • server maintenance by the DBA;
  • serialization conflicts in SERIALIZABLE or snapshot isolation transactions

  ... and more.

  Your application will have "holes" in the invoice numbering where those rollbacks occur. Additionally, there is no ordering guarantee, so it's entirely possible that a transaction with a later sequence number will commit earlier (sometimes much earlier) than one with a later number.

  Chunking:

  It's also normal for some applications, including Hibernate, to grab more than one value from a sequence at a time and hand them out to transactions internally. That's permissible because you are not supposed to expect sequence-generated values to have any meaningful order or be comparable in any way except for equality. For invoice numbering, you want ordering too, so you won't be at all happy if Hibernate grabs values 5900-5999 and starts handing them out from 5999 counting down or alternately up-then-down, so your invoice numbers go: n, n+1, n+49, n+2, n+48, ... n+50, n+99, n+51, n+98, [n+52 lost to rollback], n+97, .... Yes, the high-then-low allocator exists in Hibernate.

  It doesn't help that unless you define individual @SequenceGenerators in your mappings, Hibernate likes to share a single sequence for every generated ID, too. Ugly.

  Correct use:

  A sequence is only appropriate if you only require the numbering to be unique. If you also need it to be monotonic and ordinal, you should think about using an ordinary table with a counter field via UPDATE ... RETURNING or SELECT ... FOR UPDATE ("pessimistic locking" in Hibernate) or via Hibernate optimistic locking. That way you can guarantee gapless increments without holes or out-of-order entries.

  What to do instead:

  Create a table just for a counter. Have a single row in it, and update it as you read it. That'll lock it, preventing other transactions from getting an ID until yours commits.

  Because it forces all your transactions to operate serially, try to keep transactions that generate invoice IDs short and avoid doing more work in them than you need to.

  CREATE TABLE invoice_number ( last_invoice_number integer primary key ); -- PostgreSQL specific hack you can use to make -- really sure only one row ever exists CREATE UNIQUE INDEX there_can_be_only_one ON invoice_number( (1) ); -- Start the sequence so the first returned value is 1 INSERT INTO invoice_number(last_invoice_number) VALUES (0); -- To get a number; PostgreSQL specific but cleaner. -- Use as a native query from Hibernate. UPDATE invoice_number SET last_invoice_number = last_invoice_number + 1 RETURNING last_invoice_number;

  Alternately, you can:

  • Define an entity for invoice_number, add a @Version column, and let optimistic locking take care of conflicts;
  • Define an entity for invoice_number and use explicit pessimistic locking in Hibernate to do a select ... for update then an update.

  All these options will serialize your transactions - either by rolling back conflicts using @Version, or blocking them (locking) until the lock holder commits. Either way, gapless sequences will really slow that area of your application down, so only use gapless sequences when you have to.

  @GenerationType.TABLE: It's tempting to use @GenerationType.TABLE with a @TableGenerator(initialValue=1, ...). Unfortunately, while GenerationType.TABLE lets you specify an allocation size via @TableGenerator, it doesn't provide any guarantees about ordering or rollback behaviour. See the JPA 2.0 spec, section 11.1.46, and 11.1.17. In particular "This specification does not define the exact behavior of these strategies. and footnote 102 "Portable applications should not use the GeneratedValue annotation on other persistent fields or properties [than @Id primary keys]". So it is unsafe to use @GenerationType.TABLE for numbering that you require to be gapless or numbering that isn't on a primary key property unless your JPA provider makes more guarantees than the standard.

  If you're stuck with a sequence:

  The poster notes that they have existing apps using the DB that use a sequence already, so they're stuck with it.

  The JPA standard doesn't guarantee that you can use generated columns except on @Id, you can (a) ignore that and go ahead so long as your provider does let you, or (b) do the insert with a default value and re-read from the database. The latter is safer:

  @Column(name = "inv_seq", insertable=false, updatable=false) public Integer getInvoiceSeq() { return invoiceSeq; }

  Because of insertable=false the provider won't try to specify a value for the column. You can now set a suitable DEFAULT in the database, like nextval('some_sequence') and it'll be honoured. You might have to re-read the entity from the database with EntityManager.refresh() after persisting it - I'm not sure if the persistence provider will do that for you and I haven't checked the spec or written a demo program.

  The only downside is that it seems the column can't be made @ NotNull or nullable=false, as the provider doesn't understand that the database has a default for the column. It can still be NOT NULL in the database.

  If you're lucky your other apps will also use the standard approach of either omitting the sequence column from the INSERT's column list or explicitly specifying the keyword DEFAULT as the value, instead of calling nextval. It won't be hard to find that out by enabling log_statement = 'all' in postgresql.conf and searching the logs. If they do, then you can actually switch everything to gapless if you decide you need to by replacing your DEFAULT with a BEFORE INSERT ... FOR EACH ROW trigger function that sets NEW.invoice_number from the counter table.