Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

[Sebastien Flaesch]

Hello!

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.

When running several client processes in concurrently, using this INSERT 
statement, I get duplicate primary key errors.

The SQL code of my INSERT looks like this:

        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)

To me something is wrong with the behavior of PostgreSQL:

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

Obviously if several processes execute that code in parallel, and if the select 
last_value / setval() get mixed up, we are in trouble...

Can something confirm this is a PostgreSQL bug, or help me to find the right 
code to avoid the issue?

To reproduce:

  1.  Create tab1 table and stored procedure for testing with insert-tab1.sql
  2.  Run several psql processes in parallel, calling the stored procedure: sh 
./start2.sh (Linux/bash)

Thanks!

Seb

inserts-tab1.sql
Description: inserts-tab1.sql

call-proc.sql
Description: call-proc.sql

start2.sh
Description: start2.sh