Generators.

    Generators are a blessing and a curse, depending upon who answers the question about their usefulness. Before discussing advantages and drawbacks, I'm going to explain briefly what is a generator, because this kind of object doesn't exist on other engines.

A generator is an integer value that's created with an extension to the SQL syntax and its main property is it always has a unique value across the database and it's independent of the transactions. There is a native functions, with the syntax
rc_value = gen_id(generator_name, delta)
that can be used in triggers and stored procedures and it's also available in client applications through packages like IB_Objects. The syntax shown above modifies the generator by delta (an integer value, too) and returns the new generator's value to a variable I've named here "rc_value", of type integer or real. Calls to gen_id are guaranteed to be serialized and aren't affected if the transaction used by the caller is committed or rolled back. For this reason, generators are used mainly as counters, to generate unique numbers across the database. Note they aren't tied to an specific table: any user request/procedure/trigger can use any generator and at this time, there's no security rights (grant/revoke) to protect them, so any user allowed to connect to a database can use all generators defined in that database.

If the only need is to know the current value of a generator, passing 0 as "delta" does the trick when calling gen_id. The syntax for defining a generator is
CREATE GENERATOR generator_name
and its value defaults to zero. If other starting point is needed, the SQL command
SET GENERATOR generator_name TO value
will do the work, where "value" must be an integer. There is no "drop generator" statement. A generator uses the space of an integer (in the range -2^³¹ to 2^³¹-1 for 32-bit machines) in a special database page, so you won't run out of space. A generator can be initialized to a negative value in the allowed range. Also, "delta" can be negative, too (although often is 1), so you can count backwards using a generator.

Because IB has no concept of auto-increment fields like Paradox, usually a combination of a BEFORE INSERT trigger and a generator emulated such type of field. Care must be taken to define and use one generator per table to have steadily increasing values, namely, a sequence. This may be cumbersome but can be automated with some clever CASE tool. However, if an insertion is rejected due to a foreign constraint check that fails (they are checked by the engine after all user-defined BEFORE triggers), there will be hole in the sequence. Also, if a transaction that made 100 insertions is rolled back, there will be 100 unused values in the field that is being filled in by the trigger with the generator's value. AFAIK, the same problem happens with databases that provide auto-increment fields. There is a reason, multi user access: another user might have picked the following value and if the generator were returned to its previous value because the transaction rolled back or the insertion failed, then the next time the generator was used, it would conflict with the following value that other users already got and used.

Absolute monotonic sequences without holes are hard to achieve in multi user environments. The best solution is to run later a transaction with exclusive access to the problematic tables and fix the sequence when no user needs that table. This exclusive access is easy to get in Interbase for short periods of time. Also, remember in some applications, only an ever increasing value is needed and this value never is seen by the user. Therefore, if there're missing values in the sequence, they don't cause problems.

Generators aren't restricted to create automatic primary keys in tables. Because the calls to gen_id are serialized, it's possible to use a table with an user name and a timestamp plus a generator acting as a mutex, so you can have some sort of process control by hand inside the database. The table is used for robustness, because a failure in the connection between the user and the database before the mutex is released by that user would lead to an eternal wait for the other users relying on the same mechanism to handle special synchronization. In that case, the table will register the starting time since the user is holding the mutex so it can be reclaimed by another user if the interval is too long.

To resume, generators give great flexibility at the cost of more work but for several operations, they provide a good behavior.

This page was last updated on 2000-05-26 04:28:46