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.
