Re: Could postgres12 support millions of sequences? (like 10 million) - Mailing list pgsql-general

        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