Generator basics

<< Introduction | Firebird Generator Guide | SQL statements for generators >>

What is a generator?
What is a sequence?
Where are generators stored?
What is the maximum value of a generator?
- Client dialects and generator values
How many generators are available in one
database?
- Older InterBase^® and Firebird versions
Generators and transactions

Generator basics

What is a generator?

Think of a generator as a "thread-safe" integer counter that lives inside a Firebird database. You can create one by giving it a name:

 CREATE GENERATOR GenTest;

Then you can get its current value and increase or decrease it just like a var i:integer in Delphi, but it is not always easy to "predictably" set it directly to a certain value and then obtain that same value – it's inside the database, but outside of transaction control.

What is a sequence?

Sequence is the official SQL term for what Firebird calls a generator. Because Firebird is constantly striving for better SQL compliance, the term SEQUENCE can be used as a synonym for GENERATOR in Firebird 2 and up. In fact it is recommended that you use the SEQUENCE syntax in new code.

Although the word sequence puts the emphasis on the series of values generated whereas generator seems to refer primarily to the factory that produces these values, there is no difference at all between a Firebird generator and a sequence. They are just two words for the same database object. You can create a generator and access it using the sequence syntax, and vice versa.

This is the preferred syntax for creating a generator/sequence in Firebird 2:

 CREATE SEQUENCE SeqTest;

Where are generators stored?

Generator declarations are stored in the RDB$GENERATORS system table. Their values however are stored in special reserved pages inside the database. You never touch those values directly; you access them by means of built-in functions and statements which will be discussed later on in this guide.

Warning: The information provided in this section is for educational purposes only. As a general rule, you should leave system tables alone. Don't attempt to create or alter generators by writing to RDB$GENERATORS. (A SELECT won't hurt though.)

The structure of the RDB$GENERATORS system table is as follows:

RDB$GENERATOR_NAME CHAR(31)
RDB$GENERATOR_ID SMALLINT
RDB$SYSTEM_FLAG SMALLINT

And, as from Firebird 2.0:

RDB$DESCRIPTION BLOB subtype TEXT

Note that the GENERATOR_ID is – as the name says – an IDentifier for each generator, not its value. Also, don't let your applications store the ID for later use as a handle to the generator. Apart from this making no sense (the name is the handle), the ID may be changed after a backup-restore cycle. The SYSTEM_FLAG is 1 for generators used internally by the engine, and NULL or 0 for all those you created.

Now let's have a look at the RDB$GENERATORS table, here with a single self-defined generator:

RDB$GENERATOR_NAME	RDB$GENERATOR_ID	RDB$SYSTEM_FLAG \|
`RDB$SECURITY_CLASS`	1	1
`SQL$DEFAULT`	2	1
`RDB$PROCEDURES`	3	1
`RDB$EXCEPTIONS`	4	1
`RDB$CONSTRAINT_NAME`	5	1
`RDB$FIELD_NAME`	6	1
`RDB$INDEX_NAME`	7	1
`RDB$TRIGGER_NAME`	8	1
`MY_OWN_GENERATOR`	9	`NULL`

Firebird 2 notes:

Firebird 2 saw the introduction of an additional system generator, called RDB$BACKUP_HISTORY. It is used for the new NBackup facility.
Even though the SEQUENCE syntax is preferred, the RDB$GENERATORS system table and its columns have not been renamed in Firebird 2.

What is the maximum value of a generator?

Generators store and return 64-bit values in all versions of Firebird. This gives us a value range of:

-2⁶³ .. 2⁶³-1 or -9,223,372,036,854,775,808 .. 9,223,372,036,854,775,807

So if you use a generator with starting value 0 to feed a NUMERIC(18) or BIGINT column (both types represent 64-bit integers), and you would insert 1,000 rows per second, it would take around 300 million years (!) before it rolls over. As it is pretty unlikely mankind will still walk on this planet by then (and still use Firebird databases), that's nothing to be really worried about.

A word of warning though. Firebird speaks two SQL "dialects": dialect 1 and dialect 3. New databases should always be created with dialect 3, which is more powerful in a number of respects. Dialect 1 is a compatibility dialect, to be used only for legacy databases that were first created under InterBase^® 5.6 or earlier.

One of the differences between the two is that dialect 1 has no native 64-bit integer type available. NUMERIC(18) columns for instance are stored internally as DOUBLE PRECISION, which is a floating point type. The biggest integer type in dialect 1 is the 32-bit INTEGER.

In dialect 1 as in dialect 3, generators are 64-bit. But if you assign the generated values to an INTEGER column in a dialect 1 database, they are truncated to the lower 32 bits, giving an effective range of:

-2³¹ .. 2³¹-1 or -2,147,483,648 .. 2,147,483,647

Although the generator itself would go on from 2,147,483,647 to 2,147,483,648 and beyond, the truncated value would wrap around at this point, giving the impression of a 32-bit generator.

In the situation described above, with 1,000 inserts per second, the generator-fed column would now roll over after 25 days (!!!) and that is indeed something to keep an eye on. 2³¹ is a lot, but then again not that much depending on the situation.

Note: In dialect 3, if you assign generator values to an INTEGER field, all goes well as long as the values lie within the 32-bit range. But as soon as that range is exceeded, you get a numeric overflow error: dialect 3 is much stricter on range checking than dialect 1!

Client dialects and generator values

Clients talking to a Firebird server can set their dialect to 1 or 3, regardless of the database they are connected to. It is the client dialect, not the database dialect, that determines how Firebird passes generator values to the client:

If the client dialect is 1, the server returns generator values as truncated 32-bit integers to the client. But inside the database they remain 64-bit values and they do not wrap after reaching 2³¹-1 (even though it may look that way to the client). This is true both for dialect 1 and dialect 3 databases.
If the client dialect is 3, the server passes the full 64-bit value to the client. Again, this holds whether the database dialect is 1 or 3.

How many generators are available in one database?

Since Firebird version 1.0, the number of generators you can have in a single database is limited only by the maximum assignable ID in the RDB$GENERATORS system table. Being a SMALLINT, this maximum is 2¹⁵-1 or 32767. The first ID is always 1, so the total number of generators cannot exceed 32767. As discussed before, there are 8 or 9 system generators in the database, leaving room for at least 32758 of your own. This should be amply enough for any practical application. And since the number of generators you declare has no effect on performance, you can feel free to use as many generators as you like.

Older InterBase^® and Firebird versions

In the earliest pre-1.0 Firebird versions, as well as in InterBase^®, only one database page was used to store the generator values. Therefore, the number of available generators was limited by the page size of the database. The following table lists how many generators – including system generators – you can have in various InterBase^® and Firebird versions (thanks to Paul Reeves for providing the initial information):

Version	Page size
	1K	2K	4K	8K
InterBase^® < v.6	247	503	1015	2039
InterBase^® 6 and early pre-1.0 Firebird	123	251	507	1019
All later Firebird versions	32767

In InterBase^® versions prior to 6, generators were only 32 bits wide. This explains why these older versions could store roughly twice the number of generators on the same page size.

Warning: InterBase^®, at least up to and including version 6.01, would happily let you "create" generators until the total number reached 32767. What happened if you accessed generators with an ID higher than the number given in the table above depended on the version:

InterBase^® 6 would generate an "invalid block type" error because the calculated location lay outside the one page that was allocated to generators.
In earlier versions, if the calculated location lay outside the database, an error would be returned. Otherwise, if the generator was only read (without increment), the value that just "happened to be" on the calculated spot was returned. If it was written to, it would overwrite data. This could sometimes lead to an immediate error, but most of the time it would just silently corrupt your database.

Generators and transactions

As said, generators live outside of transaction control. This simply means you cannot safely "rollback" generators inside a transaction. There may be other transactions executing at the same time that change the value while your transaction runs. So once you have requested a generator value, consider it as "gone forever".

When you start a transaction and then call a generator and get a value of – let's say – 5, it will remain at that value even if you roll back the transaction (!). Don't even think of something like "OK, when I rollback, I can just do GEN_ID(mygen,-1) afterwards to set it back to 4". This may work most of the time, but is unsafe because other concurrent transactions may have changed the value in between. For the same reason it doesn't make sense to get the current value with GEN_ID(mygen,0) and then increment the value on the client side.