<< Creating unique row IDs | Firebird Generator Guide | A: Document history >>

What else to do with generators

Here you can find some ideas for usages of generators other than generating unique row IDs.

Using generators to give e.g. transfer files unique numbers

A "classic" usage of generators is to ensure unique, sequential numbers for – well, anything in your application other than the row IDs discussed above. When you have an application that is transferring data to some other system, you can use generators to safely identify a single transfer by labeling it with a generated value. This greatly helps tracking down problems with interfaces between two systems (and, unlike most of the following, this does work safely and exactly).

Generators as "usage counters" for stored procedures to provide basic statistics

Imagine you just built a fantastic new feature into your database with a stored procedure. Now you update your customer's systems and some time later you'd like to know if the users really use this feature and how often. Simple: make a special generator that only gets incremented in that stored procedure and you're there... with the restriction that you can't know the number of transactions that were rolled back after or while your SP executed. So in this case you at least know how often users tried to use your SP :-)

You could further refine this method by using two generators: One gets incremented at the very start of the SP, another at the very end just before the EXIT. This way you can count how many attempts to use the stored procedure were succesful: if both generators have the same value, then none of the calls to the stored procedure failed etc. Of course you then still don't know how many times the transaction(s) invoking your stored procedure were actually committed.

back to top of page

Generators to simulate SELECT COUNT (*) FROM...

There is the known problem with InterBase^ฎ and Firebird that a SELECT COUNT(*) (with no WHERE clause) from a really large table can take quite a while to execute, since the server must count "by hand" how many rows there are in the table at the time of the request. In theory, you could easily solve this problem with generators:

• Create a special "row counter" generator;
• Make a BEFORE INSERT trigger that increments it;
• Make an AFTER DELETE trigger that decrements it.

This works beautifully and makes a "full" record count needless – just get the current generator value. I stressed the "in theory" here because the whole thing goes down the drain when any INSERT statements fail, because as said those generators are beyond transaction control. Inserts can fail because of constraints (Unique Key violations, NOT NULL fields being NULL, etc.) or other metadata restrictions, or simply because the transaction that issued the INSERT gets rolled back. You have no rows in the table and still your INSERT counter climbs.

So it depends – when you know the rough percentage of INSERT that fail (you can kinda get a "feeling" for this), and you're only interested in an estimation of the record count, then this method can be useful even though it's not exact. From time to time you can do a "normal" record count and set the generator to the exact value ("re-synchronize" the generator), so the error can be kept rather small.

There are situations when customers can happily live with an info like "there are about 2.3 million records" instantly at a mouseclick, but would shoot you if they have to wait 10 minutes or more to see that there are precisely 2.313.498.229 rows...

back to top of page

Generators to monitor and/or control long-running stored procedures

When you have stored procedures that e.g. generate report outputs on large tables and/or complex joins, they can take quite a while to execute. Generators can be helpful here in two ways: they can provide you with a "progress counter" which you can poll periodically from the client side while the stored procedure runs, and they can be used to stop it:

 CREATE GENERATOR gen_spTestProgress;
 CREATE GENERATOR gen_spTestStop;

 set term ^;

 CREATE PROCEDURE spTest (...)
 AS
 BEGIN
   (...)
   for select <lots of data taking lots of time>
   do begin
     GEN_ID(gen_spTestProgress,1);
     IF (GEN_ID(gen_spTestStop,0)>0) THEN Exit;

     (...normal processing here...)
   end
 END^

Just a rough sketch, but you should get the idea. From the client, you can do a GEN_ID(gen_spTestProgress,0) asynchronously to the actual row fetching (e.g. in a different thread), to see how many rows were processed, and display the value in some sort of progress window. And you can do a GEN_ID(gen_spTestStop,1) to cancel the stored procedure at any time from the "outside".

Although this can be very handy, it has a strong limitation: it's not multi-user safe. If the stored procedure would run simultaneously in two transactions, they would mess up the progress generator – they would both increment the same counter at the same time so the result would be useless. Even worse, incrementing the stop generator would immediately stop the stored procedure in both transactions. But for e.g. monthly reports that are generated by a single module run in batch mode, this can be acceptable – as usual, it depends on your needs.

If you want to use this technique and allow users to trigger the stored procedure at any time, you must make sure by other means that the stored procedure can not be run twice. Thinking about this, I had the idea to use another generator for that: let's call this one gen_spTestLocked (assuming the initial value of 0 of course):

 CREATE GENERATOR gen_spTestProgress;
 CREATE GENERATOR gen_spTestStop;
 CREATE GENERATOR gen_spTestLocked;

 set term ^;

 CREATE PROCEDURE spTest (...)
 AS
 DECLARE VARIABLE lockcount INTEGER;
 BEGIN
   lockcount = GEN_ID(gen_spTestLocked,1);
   /* very first step: increment the locking generator */

   if (lockcount=1) then /* _we_ got the lock, continue */
   begin
     (..."normal" procedure body here...)
   end

   lockcount = GEN_ID(gen_spTestLocked,-1); /* undo the increment */

   /* make sure the gen is reset at the very end even when an exception
       happens inside the “normal” procedure body: */

   WHEN ANY DO
     lockcount = GEN_ID(spTestLocked,-1); /* undo the increment */
   exit;
 END^

Note: I'm not yet 100% sure this is absolutely multi-user safe, but it looks rock solid – as long as no EXIT occurs in the normal procedure body, for then the stored procedure would stop and quit, leaving the generator incremented. The WHEN ANY clause handles exceptions, but not normal EXITs. Then you'd have to decrement it by hand – but you could decrement the generator just before the EXIT to avoid this. Given the right precautions, I can't make up any situation where this mechanism could fail... If you can – let us know!

See also:
Generator
Stored procedure
Trigger

back to top of page
<< Creating unique row IDs | Firebird Generator Guide | A: Document history >>