Hi Gavan.
On Wed, 20 Jul 2022 at 00:10, Gavan Schneider <list.pg.gavan@pendari.org> wrote:
> On 20 Jul 2022, at 4:08, Francisco Olarte wrote:
> As a remark, in Spain bill numbers need to be gapless increasing. I
> have done it with ....
>
> One answer to this problem has been around for a while, and my version is shown below.
If I read it correctly, leaving the 700 stuff aside, this function
gives you the first free transaction_ref. This is not valid for my
problem, I need them increasing, 1-3-2 is not a valid sequence. The
trick I use is that I can have "dummy" records, which do not have real
data ( or dates, which is what, among other things, makes me need them
increasing ), so I generate 1-3-4 and then insert 2 in batch which
values adequate for legal (i.e., I can just use 1.date for 2.date ).
I think what you do is generate account numbers which should be
gapless in the medium term and should be increasing as needed, wherte
the gapless is more important to you than the increasing.
> No extra table is needed, just a windowed look at the table where the reference is used.
It is normally never needed, thay are used solely for performance and
to avoid locking. A current_transaction_seq
> My usage is at human speed so performance is not an issue but it should be pretty good if there are minimal holes.
Whatis not addressed is that a reference can be reissued upto the time the calling process commits an entry in the
tableand takes that reference out of circulation. There are different approaches to handling/preventing such
collisions.
Your construct seems pretty expensive, and I'm not sure how much
locking it does at serializable. Also, given it needs recovery ( the
single row table plus back-fill does not, if you consider the
back-filling "bussines as usual" ). Also the reissuing of a number is
a no-go in my automated systems, it would need extensive recovery, in
that case I can use a gap-less approach by simpy selecting max(id) (
and given it is indexed, using a select for update on the max record
by ordering desc and limit 1, if I'm not too confused this would give
no gaps, increasing at the expense of reduced concurrency ).
...
> $COMMENT$
> Determines the next available reference number, making sure to fill any holes.
No going back allowed in my case. Simplifying it, a bill may reference
an older one in its data, and its number MUST be less. The gap problem
is there MUST be NO gaps WHEN I "print" them. And I can use dummies,
but I SHOULD not use them. In practice, you rarely use more than a
couple of dummies a year. I could strictly print dummies when I need
them, but they MUST be always the same, and the easier way is to
insert them.
Francisco Olarte.
