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 <[email protected]>
Sent: Tuesday, July 19, 2022 5:41 PM
To: Sebastien Flaesch <[email protected]>
Cc: pgsql-general <[email protected]>
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 <[email protected]> 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
