Subject Re: [firebird-support] SELECT MAX() or Generator
Author William L. Thomson Jr.
On Thu, 2005-12-15 at 17:40 -0200, Alexandre Benson Smith wrote:
> Generator are transaction independent if you get a value and then
> rollback the generator will not return to the previous value.

So far the seperate transactions have not been an issue. Most
connections are not open long, and rarely any conncurrent activity, yet.

> Generator could but should not be set/reset by hand, this can cause
> trouble (getting duplicate values).

I remember every now and then reseting them after a delete or several.
Also having code to revert after an increment, do a -1 I believe.

> Generator should be something without meaning to the users, should not
> matter if there is gaps in the numbering sequence.

They were, but the generated # I would use at times. For some things,
but the gaps is more of a personal preference I am getting over. Code
wise gaps are irelivant.

> The only thing you could garantee using generators is that no
> transaction could get the same value twice (and that is what it should
> be used for), and if you just issue gen_id(MyGen, 1) the value will
> always be greather then the previous, no chance of collision, the value
> will grow, but you can see gaps.

Guess I should revert back, and just deal then.

> The approach you use will be fast enough if you have a descending index
> on the column used in the MAX, but will be slow on very large tables
> without that descending index.

Yes, I have decending indexes. Would a SELECT FIRST 1 using the
desending index be faster than MAX? Or basically same thing?

> But this is not the major problem, the
> major and big problem you have in your approach is that it's not
> muti-user safe !
> Two concurrent inserts could get the same values since one doesn't see
> the other.

I will address that, before it becomes an issue or problems occur.

> If you need an unbroken sequence take a look on this paper:

Thank you

William L. Thomson Jr.
Obsidian-Studios, Inc.