| Subject | Re: [firebird-support] Sequential auto incremental numbering |
|---|---|
| Author | River~~ |
| Post date | 2018-09-04T07:48:42Z |
Hi all
On Tue, 4 Sep 2018, 00:27 'Louis van Alphen' louis@... [firebird-support], <firebird-support@yahoogroups.com> wrote:As previous responders have said, generator values are guaranteed to be
unique. However when transactions fail, the generator will skip a value.
Hadn't spotted this: yes that's right and for some applications it will be important
Only relying on
NewNo = select max(No) + 1 from Invoice
is also a bad idea because in concurrent insert conditions you are for sure
going to get duplicates.
See below
As far as I remember, aggregate functions also do
not use indexes and will get progressively slower over time. This might have
changed though, so I may be wrong on this.
It's also possible that I am wrong and that i was confusing firebird with other dbms.
But I also have to say that Oracle was already using indexes to deliver min / max in the late 1980s (from Oracle v4 iirr). I would be very disappointed if fb has not yet followed that lead.
But then, as with all free-as-in-both-beer-and-speech software, my solution would be to start work on the enhancement. I don't because my priorities are different. And that means I have no right to dump my disappointment on other people who also choose to follow other priorities of their own.
The only reliable way I found is as follows:
* We know that the DB engine will at the very least guarantee
uniqueness of the invoice no if we put a unique constraint on the column.
Yes absolutely. In my previous post I was assuming that invoice number was the PK, which is implicitly unique even if not explicitly declared so.
If not, then you absolutely need a unique index. And that also makes it possible for the db engine to optimise the query by taking the max from that index. Even if fb does not currently do this you are adding the hooks for a performance boost if/when the fb devs do make the max function index aware
Thank you for feeding back to me the potential bug in my tip.
R~~