Dear All,

As I understand it, in IB6 Dialect 1 (and IB5.x) generators are using
32bit-integers; while in IB6 Dialect 3 onward, we can use 64bit-
integers.

While there have been a lot of discussion about the 'best' way to
handle Auto-Increment fields and Generators in Delphi/IB, almost
nobody touches the question of 'what would happen when Generators
reached the limit of 32bit integers'.

From my limited testing, since generators uses 32bit *signed*
integers, assuming that the sequence starts from 0, once it reached
the 2GB mark, it will 'wrap around' to -2GB and counting down from
there to zero.

This will mean that we have 4GB unique numbers in our disposal,
assuming that each record is around 100bytes, then the table size
itself is around 400GB. This back-of-the-envelope calculation makes
me feel safe for a while, since I'm sure my database won't reach that
size anytime soon, until I realize something.

Assume that we are using IBX, Generators, and a Trigger for our
table's primary key in the 'normal' fashion. New PKs are assigned in
the OnNewRecord event (or using IBDataset's generatorField), and the
before_insert trigger only assigns new PKs if the field is null.

The problem is that everytime a user INSERTs a record, *then* CANCELs
the Insert, one number is wasted. The same goes for ROLLBACK-ed
transactions, even after TIBDataset/TTable/TQuery did the actual
posting, because Generators are outside transaction controls.

Basically it came down to several issues:
1. For simplicity, let's say that we will not do any purging on the
database (usually done annually or bi-annually). What to do when
we reached the 2GB/4GB mark? I was secretly hoping that by that
time IB6 and 64bit integers will be mainstream, and I can change
the primary keys to 64bit ints and be safe for another 10 years
or so...;)

2. Is it practical to do 'garbage collection' on the unused/wasted
PKs ?

The following is another problem entirely, but important
nevertheless. If you're doing MASTER-DETAIL relationship, you can use
the above construct for the MASTER tables, but how about the DETAIL
tables. Normally you will have a compound PK in the DETAIL,
consisting of MASTER_ID and another field (LINESEQ, for example). How
do you assign unique numbers to the LINESEQ since it only need to be
unique for EACH MASTER RECORD, not globally.

On the other hand, Scott Ambler has been advocating the use of UNIQUE
OID for each object (that is, each records in our context), so if
we'd ever want to implement something like this, (useful for
implementing universal audit trails, for example) the 2GB pool
suddenly feels very cramped.

Any comments/opinions/experiences will be very much appreciated.
Thank you in advance,
sugi.