Re: Race condition in resetting a sequence

Steve Midgley <public@misuse.org> 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)

Ugh.  That's completely unsafe/broken, unless they also use locking that
you didn't show.

> You mentioned something more general though: "As long as you're using 
> setval you have a race condition"? However the postgres manual states:

>> The sequence functions, listed in 
>> <http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html#FUNCTIONS-SEQUENCE-TABLE>Table 
>> 9-34, provide simple, multiuser-safe methods for obtaining successive 
>> sequence values from sequence objects.

> (http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html)

> Included in Table 9-34 is "setval" - so I'm not clear how it can have a 
> race condition all by itself?

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.
        regards, tom lane