Hi all

On Tue, 4 Sep 2018, 00:27 'Louis van Alphen' lo...@nucleo.co.za
[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

> 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

Thank you for feeding back to me the potential bug in my tip.


  • Re: [fire... Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support]
    • RE: ... 'Christian Giesen' ch...@xt.co.za [firebird-support]
    • Re: ... 'River~~' river14ap...@gmail.com [firebird-support]
      • ... Ann Harrison aharri...@ibphoenix.com [firebird-support]
        • ... 'Louis van Alphen' lo...@nucleo.co.za [firebird-support]
          • ... 'River~~' river14ap...@gmail.com [firebird-support]
    • RE: ... liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
    • Re: ... Gabor Boros mlngl...@bgss.hu [firebird-support]
      • ... Ann Harrison aharri...@ibphoenix.com [firebird-support]

Reply via email to