Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com> 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