On Fri, Mar 20, 2020 at 3:59 PM Peter J. Holzer <hjp-pg...@hjp.at> wrote:
> On 2020-03-19 16:48:19 -0700, David G. Johnston wrote: > > First, it sounds like you care about there being no gaps in the records > you end > > up saving. If that is the case then sequences will not work for you. > > I think (but I would love to be proven wrong), that *nothing* will work > reliably, if > > 1) you need gapless numbers which are strictly allocated in sequence > A little gap is acceptable. We cannot afford a 100 gap though. 2) you have transactions > 3) you don't want to block > > Rationale: > > Regardless of how you get the next number, the following scenario is > always possible: > > Session1: get next number > Session2: get next nummber > Session1: rollback > Session2: commit > > At this point you have a gap. > > If you can afford to block, I think a simple approach like > > create table s(id int, counter int); > ... > begin; > ... > update s set counter = counter + 1 where id = $whatever returning > counter; > -- use counter > commit; > > should work. But that effectively serializes your transactions and may > cause some to be aborted to prevent deadlocks. > > hp > > -- > _ | Peter J. Holzer | Story must make more sense than reality. > |_|_) | | > | | | h...@hjp.at | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
