Re: Race condition in resetting a sequence

Steve Midgley <public(at)misuse(dot)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