Re: Race condition in resetting a sequence

Steve Midgley writes:
>> The code I provided to reset a primary key sequence is actually part of
>> Ruby on Rails core library - actually they use something very similar
>> to what I originally sent:
...
>> SELECT setval('#{sequence}', (SELECT COALESCE(MAX(#{pk})+(SELECT
>> increment_by FROM #{sequence}), (SELECT min_value FROM #{sequence}))
>> FROM #{table}), false)

Tom Lane wrote:
> Ugh. That's completely unsafe/broken, unless they also use locking that
> you didn't show.
...
> It doesn't have a race condition "all by itself": it will do what it's
> told. The problem with commands such as the above is that there's a
> time window between calculating the max() and executing the setval(),
> and that window is more than large enough to allow someone else to
> insert a row that invalidates your max() computation. (Because of MVCC
> snapshotting, the risk window is in fact as long as the entire
> calculation of the max --- it's not just a few instructions as some
> might naively think.)
>
> Now it is possible to make this brute-force approach safe: you can lock
> the table against all other modifications until you've applied your own
> changes. But you pay a high price in loss of concurrency if you do
> that.

All this trouble over semantically-significant ID columns seems to support the
camp that excoriates use of artificial ID columns and autoincrementation
altogether.

The usual argument in their favor is that they speed up performance, but this
epicyclic dance to accomodate FK references to autoincremented keys makes the
case that there is also a performance penalty, and in the more critical
performance area of code development and correctness than in the less critical
search speed area.

--
Lew