From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Could postgres12 support millions of sequences? (like 10 million) |
Date: | 2020-03-20 22:58:42 |
Message-ID: | 20200320225842.GA18976@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2020-03-20 23:11:42 | Re: Could postgres12 support millions of sequences? (like 10 million) |
Previous Message | pabloa98 | 2020-03-20 21:13:18 | Re: Could postgres12 support millions of sequences? (like 10 million) |