How to generate sequential number sequences in the database?

Question

I have a table and a generator in the database. This table has a primary key which is an Integer. If the user tries to insert records in the table, hibernate does a select from the sequence in the database, thus it makes the sequence to increase itself. The problem is, if some error occurs while committing the transaction, the sequence is already increased, and the next record to be saved won't have it's primary key with a consistent sequence comparing to the last record inserted before.

Is there a way to solve this?

-------- EDIT ---------

I managed to create a trigger that does this job:

CREATE TRIGGER TBLTESTE_BI0 FOR TBLTEST
ACTIVE BEFORE INSERT POSITION 0
AS
    declare variable next_value integer;
begin
    select coalesce(max(my_sequence_field+1),1) from tbltest into :next_value;
    new.my_sequence_field=:next_value;
end

I'm not going to use this as the primary key of course, the primary key will still be generated by the sequence generator. But just a question, is it guaranteed that the my_sequence_field will have the correct sequence, with no repeated values? Consider that the value will be set only by the trigger.

this is pretty standard behavior in databases. if you think you need your ids to be sequential numbers without gaps, you should probably re-think your requirements. you could probably get it to work, but it would be slow and fragile. — jtahlborn, Jan 31 '14 at 13:27
The generated ID of a row is **completely** meaningless. You don't have to care about gaps in that sequence because it doesn't matter if a row gets the ID 42, 10436 or 736534645 as long as the ID is unique. If your application relies on gaplass sequences then there is something wrong in your application — , Jan 31 '14 at 13:29
Hibernate's sequence generation optimizers insert gaps even in normal behavior: the database's sequence is multiplied by, say, 100, and a single `nextval` "checks out" a packet of 100 IDs, which may or may not get used. — Marko Topolnik, Jan 31 '14 at 13:31
The easiest way might be to select the max(id) value, and then use it at the next insert — maja, Jan 31 '14 at 13:32
@maja: that is virtually guaranteed to generate dup id related errors short of locking the table. — Denis de Bernardy, Jan 31 '14 at 13:33
@Denis: not, if you use correct transaction isolation levels — maja, Jan 31 '14 at 13:36
@maja: I'll take that bet. :-) https://gist.github.com/ddebernardy/8732389 — Denis de Bernardy, Jan 31 '14 at 13:51
@maja: it's a primary key. that's sql jargon for unique not null (and clustered). — Denis de Bernardy, Jan 31 '14 at 13:58
@Denis: yes, that's true (in except, that `clustered` is only the default-behaviour. You can put the cluster on any other column if you want) — maja, Jan 31 '14 at 14:07
Avoid designing your application to require gapless sequences. If you need them, don't make them a primary key. Search for "postgresql gapless sequence". See also this: http://stackoverflow.com/a/9985219/398670 — Craig Ringer, Feb 01 '14 at 04:26
@maja: there is no "clustered" primary key (or index) in Postgres — , Feb 01 '14 at 12:06

score 4 · Accepted Answer · answered Jan 31 '14 at 14:12

Consider these three desirable properties:

Adding a new row does not require serialisation to prevent other rows being added at the same time.
Assigning a new unique identifier can occur when the row is inserted.
The unique key is assigned a strictly sequential number without any gaps.

You can only have two of these attributes in your system. If you want your numbers to be strictly sequential then you have to either lock the table for new inserts, or populate the unique value asynchronously after the row has been inserted.

Similarly, if you want to avoid serialising the creation of new rows, then you have to either defer the assignment of the new unique identifier, or you do not assign strictly sequential values.

Items one and two are generally far more important for synthetic primary keys. If you have a need to generate strictly sequential values (for example, invoice numbers must sometimes be sequential by law), then you you would generally drop one of the first two attributes.

What if i use a trigger to do the job? Please check my question to see the edit. — Mateus Viccari, Feb 01 '14 at 11:08
It doesn't matter what the mechanism is -- you cannot generate sequential numbers at the time of insert without a locking mechanism. — David Aldridge, Feb 01 '14 at 17:38

score 1 · Answer 2 · edited May 23 '17 at 10:27

As David explains, primary keys are not really suitable when "you have a need to generate strictly sequential values". If you have this requirement, you could use select for update.

For example, in the case of invoice numbers use an extra table with the "last invoice number" and synchronize the inserts on that. In pseuso-code:

start transaction
// Lock the sequence record.
// All other transaction for the same debtor have to wait for this lock.
select id, lastnumber from invoicenumbers where debtor=1 for update
ResultSet rs = ps.executeQuery();
long lastNumberId = rs.getLong(1);
int lastNumber = rs.getInt(2);
insert into invoices (debtor, invoice_id, amount) values (1, ?, 1)
// Increase sequence with each insert
ps.setInt(1, ++lastNumber);
ps.executeUpdate();
// Update the sequence number
update invoicenumbers set lastnumber=? where id=?
ps.setInt(1, lastNumber);
ps.setLong(2, lastNumberId);
ps.executeUpdate();
commit transaction ON ERROR rollback

Example tables:

create table invoicenumbers
(
id bigserial primary key,
debtor int,
lastnumber int
);
create table invoices
(
id bigserial primary key,
debtor int,
invoice_id int,
amount int
);

How to generate sequential number sequences in the database?

2 Answers2