---------- Original Message -----------
From: "Ann W. Harrison" <aharrison@...>

> It doesn't happen in Firebird. MySQL - at least the MyISAM engine -
> does notice that you've stored a record without using the auto increment
> value and increases the automatically generated number to compensate.
> It's amazing the tricks you can do when you don't care about transaction
> isolation.
>
> Best,
>
> Ann

------- End of Original Message -------

An update/insert trigger (in FB) could be written to do the following, no?

if(new.pk is null) then
new.pk = gen_id(gen_name, 1);
else if (new.pk > gen_id(gen_name, 0)) then
gen_id(gen_name, new.pk - gen_id(gen_name, 0));

(I probably have some off-by-one bugs in there.) The idea is to detect a bald
insert, and if it could cause problems later, go ahead and increment the
generator *at least* enough to get it past the known value; if someone else is
inserting concurrently, they both cause (cumulative) increases, and that's okay
in the long run, it just leaves gaps, but gaps were extremely likely anyway.
Bonus: multiple triggers could all be using/modifying the same generator, and
they'd all stay synchronized and non-overlapping despite abuses, which is more
than MySQL could claim. Or I need more caffeine.

-Philip