Re: Could postgres12 support millions of sequences? (like 10 million)

On Mon, Mar 23, 2020 at 9:58 AM Daniel Verite <daniel(at)manitou-mail(dot)org>
wrote:

> pabloa98 wrote:
>
> > When I have a medium number of sequence I will report how it behaves. It
> > will take some time though.
>
> Be aware that creating the sequences on the fly has the kind of race
> condition that you wanted to avoid in the first place.
>
> For instance consider this execution in two concurrent sessions:
>
> S1: BEGIN;
>
> S1: CREATE SEQUENCE seq1 IF NOT EXISTS;
>
> S2: BEGIN;
>
> S2: CREATE SEQUENCE seq1 IF NOT EXISTS;
> S2: (now blocked waiting for S1)
>
> S1: COMMIT;
>
> S2: ERROR: duplicate key value violates unique constraint
> "pg_type_typname_nsp_index"
> DETAIL : Key (typname, typnamespace)=(seq1, 36434) already exists.
>
> The client could catch these errors and retry, but then it could also
> do that with serializable transactions on serialization failures
> (SQLSTATE 40001), and you'd get the guarantee of consecutive
> numbering without creating all these sequences, not to mention
> the protection against other potential concurrency anomalies.
> See https://www.postgresql.org/docs/current/transaction-iso.html
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>

Indeed. We have a "creation state" in the application. During that moment
the pair(group,element) and the sequence will be created.
After the creation process is done, the entry is in "production state" and
the sequence will be used only then.

Pablo