On Fri, Mar 20, 2020 at 3:59 PM Peter J. Holzer <hjp-pgsql@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. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
