Subject Re: [firebird-support] Re: Generators for auto-increment - with override
Author Daniel L. Miller
Alan McDonald wrote:

>>Assuming I trust any manually specified id - sometimes I'm paranoid
>>enough that I don't even trust myself. The max() check was the simplest
>>way I came up with to guarantee the validity of the id.
>I still have to question your motive and your design though. You allow
>manually specified IDs only to change them later if it doesn't suit your
>backend. Why? Why not just manage your own IDs and not let anyone specify
>them at all?
Granted I sometimes carry theory to extremes, but . . .

For our hypothetical example, let's say I have an application that needs
to know the id of a new row prior to insertion. This is, of course, one
of the wonderful benefits of Firebird's generators.

So my application uses the gen_id function, gets a new id, and uses it
for further processing, user interaction, data entry, whatever. Now
it's time to finally store the row. So the application performs the
insert with the stored id.

Now, just for the sake of argument, let's pretend you're a programmer
who's working odd hours and gets a little careless - and you somehow
perform some operation on that id number. Unlikely - hopefully, but

So now instead decrementing the logged-in user count, the primary id
gets changed - and now we try to write to the database.

Now I grant you that having an error result pop up at this point is
probably a good thing - nevertheless for a production database
application I would prefer the database to accomodate it, probably log
the error, and move on - NOT display some bizarre error message to a
user (like, "Your dumb programmer screwed up again - tell him to fix it!").

One principle I try to program by is never trust anything out of a given
environment's control - even if at the moment I control the
environment. I can remember having problems with Access (gasp, groan)
autoincrement fields getting poked too much - the reliability of
Firebird's trigger-based fields is just too wonderful to let slide
without taking advantage of them.

Sometime soon, if someone doesn't beat me to it, I'll try to run some
kind of benchmark to find out just how much overhead (meaning increased
execution time) some of these checks incur.