From: | pabloa98 <pabloa98(at)gmail(dot)com> |
---|---|
To: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Could postgres12 support millions of sequences? (like 10 million) |
Date: | 2020-03-21 02:53:43 |
Message-ID: | CAEjudX6ZBEMk0onUUwBKGrj_CnzFbUOAyxT0a9s-Xi8Yij-CWg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Mar 20, 2020 at 3:59 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)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(at)hjp(dot)at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>
From | Date | Subject | |
---|---|---|---|
Next Message | pabloa98 | 2020-03-21 03:13:03 | Re: Could postgres12 support millions of sequences? (like 10 million) |
Previous Message | Adrian Klaver | 2020-03-21 00:55:40 | Re: Could postgres12 support millions of sequences? (like 10 million) |