How do I not waste Generator values when using them server side with Firebird?

Question

Check this simple piece of code that uses a generator to create unique primary keys in a Firebird table:

CREATE OR ALTER TRIGGER ON_BEFOREINSERT_PK_BOOKING_ITEM FOR BOOKING_ITEM BEFORE INSERT POSITION 0
  AS
  BEGIN
    IF ((NEW.booking_item_id IS NULL) OR (NEW.booking_item_id = 0)) THEN BEGIN
      SELECT GEN_ID(LastIdBookingItem, 1) FROM RDB$DATABASE INTO :NEW.booking_item_id;
    END
  END!

This trigger grabs and increments then assigns a generated value for the booking item id thus creating an auto-incremented key for the BOOKING_ITEM table. The trigger even checks that the booking id has not already been assigned a value.

The problem is the auto-incremented value will be lost (wasted) if, for some reason, the BOOKING_ITEM record cannot be posted.

I have a couple of ideas on how to avoid this wasting but have concerns about each one. Here they are:

1. Decrement the counter if a posting error occurs. Within the trigger I set up a try-except block (do try-except blocks even exist in Firebird PSQL?) and run a SELECT GEN_ID(LastIdBookingItem, -1) FROM RDB$DATABASEon post exceptions. Would this work? What if another transaction sneaks in and increments the generator before I decrement it? That would really mess things up.

2. Use a Temporary Id. Set the id to some unique temp value that I change to the generator value I want on trigger AFTER INSERT. This method feels somewhat contrived and requires a way that ensures that the temp id is unique. But what if the booking_item_id was supplied client side, how would I distinguish that from a temp id?. Plus I need another trigger

3. Use Transaction Control. This is like option 1. except instead of using the try-except block to reset the generator I start a transaction and then roll it back if the record fails to post. I don't know the syntax for using transaction control. I thought I read somewhere that SAVEPOINT/SET TRANSACTION is not allowed in PSQL. Plus the roll back would have to happen in the AFTER INSERT trigger so once again I need another trigger.

Surely this is an issue for any Firebird developer that wants to use Generators. Any other ideas? Is there something I'm missing?

Answer 1

Sequences are outside transaction control, and meddling with them to get 'gapless' numbers will only cause troubles because another transaction could increment the sequence as well concurrently, leading to gaps+duplicates instead of no gaps:

• start: generator value = 1
• T1: increment: value is 2
• T2: increment: value is 3
• T1: 'rollback', decrement: value is 2 (and not 1 as you expect)
• T3: increment: value is 3 => duplicate value

Sequences should primarily be used for generating artificial primary keys, and you shouldn't care about the existence of gaps: it doesn't matter as long as the number uniquely identifies the record.

If you need an auditable sequence of numbers, and the requirement is that there are no gaps, then you shouldn't use a database sequence to generate it. You could use a sequence to assign numbers after creating and committing the invoice itself (so that it is sure it is persisted). An invoice without a number is simply not final yet. However even here there is a window of opportunity to get a gap, eg if an error or other failure occurs between assigning the invoice number and committing.

Another way might be to explicitly create a zero-invoice (marked as cancelled/number lost) with the gap numbers, so that the auditor knows what happened to that invoice.

Depending on local law and regulations, you shouldn't 're-use' or recycle lost numbers as that might be construed as fraud.

You might find other ideas in "An Auditable Series of Numbers". This also contains a Delphi project using IBObjects, but the document itself describes the problem and possible solutions pretty well.

Answer 2

What if, instead of using generators, you create a table with as many columns as the number of generators, giving each column the name of a generator. Something like:

create table generators
(
 invoiceNumber  integer default 0 not null,
 customerId     integer default 0 not null,
 other generators...
)

Now, you have a table where you can increment invoice number using a SQL inside a transaction, something like:

begin transaction
  update generator set invoiceNumber = invoiceNumber + 1 returning invoiceNumber;
  insert into invoices set ..........  
end transaction. 

if anything goes wrong, the transaction would be rolled-back, together with the new invoice number. I think there would be no more gaps in the sequence.

Enio