Firebird Generator Guide: A guide on how and when to use generators in Firebird

This article explains what Firebird generators are, and how and why you should use them. It is an attempt to collect all relevant information about generators in a single document.

Read this article if you…​

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:

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.

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

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.

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

And, as from Firebird 2.0:

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:

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

-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 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. 2³¹ is a lot, but then again not that much depending on the situation.

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.

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.

The name of a generator must be a usual DB meta-identifier: 31 chars maximum, no special characters except the underscore ‘_’ (unless you use quoted identifiers). The SQL commands and statements that apply to generators are listed below. Their use will be discussed in some detail in the section Use of generator statements.

DDL (Data Definition Language) statements:

DML (Data Manipulation Language) statements in client SQL:

DML statements in PSQL (Procedural SQL, available in stored procedures and triggers):

The availability of statements and functions depends on whether you use them in:

The answer to this question would go far beyond the scope of this article. If you see no need to have a generic, unique “handle” for every row inside a table, or don’t like the idea of “meaningless” or “surrogate” keys in general, you should probably skip this section…​

OK, so you want row IDs. { author’s note: congratulations! :-) }

A major, basic decision to take is whether we’ll use one single generator for all the tables, or one generator for each table. This is up to you — but take the following considerations into account.

With the “one for all” approach, you:

With the “one for each” approach you:

Well — yes, technically you can. But — NO, you shouldn’t. Never. Never ever. Not only that this would destroy the nice chronological sequence (you can’t judge a row’s “age” by just looking at the ID any more), the more you think about it the more headaches it’ll give you. Moreover it is an absolute contradiction to the entire concept of unique row identifiers.

So unless you have good reasons to re-use generator values, and a well-thought-out mechanism to make this work safely in multi-user/multi-transaction environments, JUST DON’T DO IT!

Giving a newly inserted record an ID (in the sense of a unique “serial number”) is easily done with generators and Before Insert triggers, as we’ll see in the following subsections. We start with the assumption that we have a table called TTEST with a column ID declared as Integer. Our generator’s name is GIDTEST.

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

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 SP 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 SP were successful: if both generators have the same value, then none of the calls to the SP failed etc. Of course you then still don’t know how many times the transaction(s) invoking your SP were actually committed.

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:

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 Inserts 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…​

When you have SPs 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 SP runs, and they can be used to stop it:

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 SP 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 SP 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 SP 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 SP at any time, you must make sure by other means that the SP 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):