Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

[Sebastien Flaesch <sebastien.flaesch@xxxxxxx>]

Changing a bit the CASE/WHEN logic in the RETURNING clause solves the issue when concurrently inserting rows without specifying explicitly a value for the serial column (INSERTs specifying values for the serial column are seldom used by concurrent programs inserting many rows):

        insert into tab1 (name) VALUES ('aaa')

            returning tab1.pkey,

                      (select case

                         when tab1.pkey>(select last_value from public.tab1_pkey_seq)

                              then setval('public.tab1_pkey_seq',tab1.pkey,true)

                         else 0

                       end)

Example (assuming there is no unique constraint!):

1. INSERT without value for serial column => sequence=0+1 => last_value = 1
   
2. INSERT without value for serial column => sequence=1+1 => last_value = 2
3. INSERT with value 2 for serial column => tab1.pkey(2) > last_value(2) ? false => no sequence reset
4. INSERT without value for serial column => sequence=2+1 => last_value = 3
5. INSERT with value 4 for serial column => tab1.pkey(4) > last_value ? true => setval(seqname,4,true)
6. INSERT without value for serial column => sequence=4+1 => last_value = 5

This will also save setval() calls for each INSERT not specifying a value explicitly for the serial column.

Stop me if I am wrong... 🙂

Seb

Seb

---

From: Tom Lane <tgl@xxxxxxxxxxxxx>
Sent: Tuesday, July 19, 2022 5:41 PM
To: Sebastien Flaesch <sebastien.flaesch@xxxxxxx>
Cc: pgsql-general <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

 

EXTERNAL: Do not click links or open attachments if you do not recognize the sender.

Sebastien Flaesch <sebastien.flaesch@xxxxxxx> writes:
> I try to update the underlying sequence of a SERIAL column, by using a RETURNING clause in my INSERT statement, which is checking that the column value is greater than the last_value of my sequence, and reset the sequence with setval() if needed.

It's not too surprising that that doesn't work, if you're coding it
based on this assumption:

> The whole INSERT statement (including the code in the RETURNING clause), should execute in a ATOMIC manner.

Sequence-related actions are always carried out immediately, they do
not participate in any atomicity guarantees about the calling transaction.
Without this, any sequence update would have to block all concurrent
uses of that sequence until they see whether the first update commits.

If that's the behavior you want, you can build it out of standard SQL
facilities (e.g. update a one-row table).  The point of sequence objects
is exactly to provide a feature with better concurrent performance,
at the cost of no rollback guarantees.

So, there's no bug here, and calling it one isn't going to change
anybody's mind about that.

                        regards, tom lane