Hi Gavan. On Wed, 20 Jul 2022 at 00:10, Gavan Schneider <list.pg.gavan@xxxxxxxxxxx> 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. What is not addressed is that a reference can be reissued upto the time the calling process commits an entry in the table and 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.
