> On Mar 20, 2020, at 4:59 PM, Peter J. Holzer <hjp-pgsql@xxxxxx> 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 > 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 OP has said small gaps are ok. To me that says the requirement is capricious but we haven’t heard the rationale for the requirement yet (or I missed it) Aside: apologies for the empty message earlier
