Creating an auto increment field in Firebird [CORE3198]

[firebird-automations]

Submitted by: Johny Oldman (johnyoldman)

After reading several documentations about Firebird, I concluded that the solution to the missing auto-incremented field feature in Firebird is to create a trigger and a generator to simulate an auto-incremented field. This solution, I am quite certain, is the one recommended by Firebird project team and is considered as enough. However, in this writing, I would like to address some problems concerning this technique. First, I will describe the solution suggested by the Firebird developer team, and then I shall show the weakness of the solution. Let us first create a simple table with two columns. One contains an id and the other contains a name. We will write the SQL statement as below:

CREATE TABLE tblCustomer
\(
    CSID NUMERIC\(18,0\) PRIMARY KEY,
    CSNAME CHAR\(30\) NOT NULL UNIQUE
\);

Now supposed we want the CSID field to be incremented automatically to avoid duplication. We will create a generator for this field.

CREATE GENERATOR genCSID;
SET GENERATOR genCSID TO 0;

And a trigger to active the generator each time a record is inserted into the table:

SET TERM ^ ;

CREATE TRIGGER trigCSID FOR tblCustomer
    ACTIVE 
    BEFORE INSERT
    POSITION 0
AS
BEGIN
    NEW\.CSID = GEN\_ID\(genCSID, 1\);
END^

SET TERM ; ^

The trigger trigCSID will get activated everytime we insert a new value into tblCustomer. And it will get generator genCSID incremented along with the execution of the trigger. It looks like nothing is wrong with the technique as long as the insertion successfully executes. But problems may arise if the insertion got cancelled on some conditions such as an exception is raised by the server, or if our application cancel the transaction by issuing a ROLLBACK statement. Under these circumstances the value of the generator will not be reverted to the previous value before the execution of the insert statements. This can be a serious bug in our application. One way to get around this problem is by providing a unique id manually for each new record in the table which has a supposedly auto-incremented field. The procedure is described below. First we create a table to hold the value of each auto incremented field in our tables. Let's just call this table tblCounters. In the previous example, since we only use one table, there is only one field which value needs to be stored in tblCounters, therefore we only need one column in tblCounters. The SQL statements to create tblCounters will be:

CREATE TABLE tblCounters
\(
    CSID NUMERIC\(18,0\)
\);

After creating the table, insert one row to record the value of the initial CSID field of tblCustomer. This will be the only row in the tblCounters table.

INSERT INTO tblCounters values \(0\);

Then we create a trigger to supply the CSID field of the tblCustomer table when there is an insertion on this table:

CREATE TRIGGER trigCSID FOR tblCustomer
    BEFORE INSERT
    POSITION 0
AS 
    DECLARE VARIABLE lCounter NUMERIC\(18,0\);
BEGIN 
    SELECT CSID from tblCounters into lCounter;
    NEW\.CSID = lCounter;
END^

This trigger will get activated before the insertion on tblCustomer table applied physically. It will give the CSID field of tblCustomer table a value based on the current value of CSID field in tblCounters. After the insertion took place, we shall increment the value of CSID field in tblCounters so that a new insertion on tblCustomer will receive a new value of CSID. This is done by creating a trigger that should be activated after the insertion occurred.

CREATE TRIGGER trigCSIDInserted FOR tblCustomer
    AFTER INSERT
    POSITION 0
AS 
BEGIN
    UPDATE tblCounters
        SET CSID = NEW\.CSID \+ 1;
END^

This trigger will get activated after the insertion successfully executed. If somehow, we encounter an exception during the insertion, this trigger will not execute as well, maintaining its CSID value before the insertion. And if our application intentionally issues a ROLLBACK statement, it will also get its action on CSID field of the tblCounters reverted.

This article can be found at http://pulse.yahoo.com/_RCDJI62T33IB66U44BUSJTYWRA/blog/

[firebird-automations]

Commented by: @dyemanov

Have you followed CORE1385? It is about the easier way, but it's still generator based.

[firebird-automations]

Commented by: @paulvink

Johnny, the purpose of generated IDs (whether with an auto-increment field or with a trigger calling a generator) is not to produce an unbroken sequence, but to ensure that each record has a unique ID.

The fact that the generator is not decremented after an exception or a rollback is not a "serious bug" but a Good Thing (TM). Imagine the damage if, after the ID is assigned, other transactions have also called the generator, and successfully committed. If decremented, the generator would then have a value that has been issued before.

BTW, how do you think auto-increment fields behave in other RDBMSes if there are multiple transactions going on and one of them suffers an exception or is rolled back?

All that being said, auto-increment or identity fields are a lot easier than Firebird's generator-and-trigger approach, which is straightforward enough but tedious if you have to do it over and over again. Just don't expect them to solve your problem if you absolutely, positively need an unbroken sequence.

[firebird-automations]

Commented by: @NormanDunbar

Morning Johny,

>> But problems may arise >> if the insertion got cancelled on some conditions such as an >> exception is raised by the server, or if our application >> cancel the transaction by issuing a ROLLBACK statement. >> Under these circumstances the value of the generator will >> not be reverted to the previous value before the execution >> of the insert statements. This can be a serious bug in our >> application.

Not exactly a serious bug in your application because it is a fact that generators/sequences are outside of the normal transaction that your application uses and are not guaranteed to provide a complete unbroken sequence of numbers.

The same "problem" occurs on Oracle databases as well.

>> One way to get around this problem is by providing a unique >> id manually for each new record in the table which has a >> supposedly auto-incremented field. The procedure is described below.

If I had a £1.00 for every time I've had to fix performance problems in systems which are "written to be database independent" that use exactly this approach, I wouldn't be at work today! ;-)

>> ...

>> CREATE TRIGGER trigCSID FOR tblCustomer >> BEFORE INSERT >> POSITION 0 >> AS >> DECLARE VARIABLE lCounter NUMERIC(18,0); >> BEGIN >> SELECT CSID from tblCounters into lCounter; >> NEW.CSID = lCounter; >> END^

What happens when two people try to insert a new customer record at the same time? The CSID value is the same for both - you cannot see my changes until I commit and I cannot see yours. Until the first one of us commits, we both have the same number.

But worse is to come ....

>> ...

>> After the insertion took place, we shall increment the value >> of CSID field in tblCounters so that a new insertion on >> tblCustomer will receive a new value of CSID. This is done >> by creating a trigger that should be activated after the >> insertion occurred. >> >> CREATE TRIGGER trigCSIDInserted FOR tblCustomer >> AFTER INSERT >> POSITION 0 >> AS >> BEGIN >> UPDATE tblCounters >> SET CSID = NEW.CSID + 1; >> END^

So, each and every table I need to provide an ID number for now has two triggers executing instead of one? But worse is still to come ....

>> This trigger will get activated after the insertion >> successfully executed. If somehow, we encounter an exception >> during the insertion, this trigger will not execute as well, >> maintaining its CSID value before the insertion. And if our >> application intentionally issues a ROLLBACK statement, it >> will also get its action on CSID field of the tblCounters reverted.

So, I'm inserting a customer and you are too. However, just after inserting my new customer but before the application (or indeed, I myself) activates a commit, I decide to leave the room for a "comfort break" leaving my desktop locked but still in the middle of inserting a customer.

Now everyone else trying to insert a new customer has a problem because they will all see my number until I commit. Equally, your session (and everyone else's) who have attempted to create a new customer while I'm still chatting at the coffee machine on my way back to my desk will hang because I've updated the value in tblCounters but I have yet to commit. Major problem - and I've dealt with exactly this scenario in an application based on Oracle (I admit) but it's the same problem.

Even better, let's say I return from the coffee machine and decide to commit my change. My transaction is now complete, and the first session in the queue waiting for me now can commit theirs - oops! Duplicate key on tblCustomer.

So, I've set up the two tables as you described and in one session I've done the following:

SQL> insert into tblCustomer(csname) values ('Norman Dunbar');

SQL> select * from tblCustomer;

             CSID CSNAME

===================== ============================== 0 Norman Dunbar

SQL> select * from tblCounters;

             CSID

===================== 1

Then, in another session, I've done this:

SQL> select * from tblCustomer; <Nothing selected>

SQL> select * from tblCounters;

             CSID

===================== 0

So straight away, you can see the problem. You see CSID zero and I see CSID 1. Next, I insert a new customer:

SQL> insert into tblCustomer(csname) values ('Johny Oldman');

And my session hangs. The Johny session is waiting for the Norman session to complete and either commit or rollback. So, after 10 or 15 minutes, I return from my comfort break and complete the work to create a new customer. And commit:

SQL> commit;

So far so goon, the Norman session has completed and a new customer exists. What about the Johny session? Well, it is now displaying the following error message:

Statement failed, SQLCODE = -803 violation of PRIMARY or UNIQUE KEY constraint "INTEG_83" on table "TBLCUSTOMER"

So, we have an even bigger problem that the one caused by a gap in the sequence of ID numbers. We have poor performance in the application, we have errors and rejected customers etc when creating new ones.

Sorry, to my mind, this is not a good solution.

In order for this to work you need an AUTONOMOUS TRANSACTION wrapped around the fetching of the sequence number from tblCounters. That way you can commit instantly you get the number and update it - however, we are now in exactly the same situation as before - gaps will appear if the main "insert customer" transaction gets rolled back.

Cheers, Norman.

[firebird-automations]

Commented by: Johny Oldman (johnyoldman)

Right...

[firebird-automations]

Commented by: @NormanDunbar

Evening Johny,

>> BEGIN >> SELECT CSID FROM tblCounters INTO lCounter; >> NEW.CSID = BIN_SHL(GEN_ID(genCSID, 1), 32) + lCounter; >> END^

You are getting a 64 bit value from a sequence, shifting it left 32 bits and adding a value obtained from the counter table? You have just lost the top 32 bits of the generator value.

You mentioned losing too many IDs if lots of transactions rolled back, ok, 64 bits is 1.84467440737e+19 different values. At one per second, that's quite a few centuries if my arithmetic still works! (I get 584,542,046,091 years given 365.25 days per year.)

Cheers, Norman.

[firebird-automations]

Commented by: Johny Oldman (johnyoldman)

Hi Norman,

I already cancelled my last post....

Apparently I forgot doing a little arithmetic before posting this issue. Case closed.

Thanks all...

How do I close this issue? Perhaps the moderator should be the one to do it....

[firebird-automations]

Modified by: @dyemanov

status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Won't Fix \[ 2 \]

[firebird-automations]

Modified by: @pcisar

status: Resolved \[ 5 \] =\> Closed \[ 6 \]