On Mon, Mar 23, 2020 at 9:58 AM Daniel Verite <daniel@xxxxxxxxxxxxxxxx> 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
