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_NAMECHAR(31)

RDB$GENERATOR_IDSMALLINT

RDB$SYSTEM_FLAGSMALLINT

And, as from Firebird 2.0:

RDB$DESCRIPTIONBLOB 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:

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 1000 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:

-231 ..
231-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 1000 inserts per second,
the generator-fed column would now roll over after 25
days (!!!) and that is indeed something to have an
eye on. 231 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 231-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
215-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

IB 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.