How to generate sequential number sequences in the database?

0
votes

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.

2
javasqldatabasehibernatepostgresql
Why do you want to have a consistent sequence id IDs? - Ruben Serrate
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
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 - a_horse_with_no_name
@maja: that is virtually guaranteed to generate dup id related errors short of locking the table. - Denis de Bernardy
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: stackoverflow.com/a/9985219/398670 - Craig Ringer

2 Answers

4
votes

Consider these three desirable properties:

  1. Adding a new row does not require serialisation to prevent other rows being added at the same time.
  2. Assigning a new unique identifier can occur when the row is inserted.
  3. 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.

1
votes

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
);