To: Steve Midgley <public@misuse.org>
cc: "Scott Marlowe" <scott.marlowe@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: Race condition in resetting a sequence 
In-reply-to: <20070803221508.2EF819FB37F@postgresql.org> 
References: <20070803165058.020509FBF2F@postgresql.org>
	<dcc563d10708031201r1e741c13od9abd2eadb0e95cf@mail.gmail.com>
	<46b381f2.0b86460a.3a8c.5460SMTPIN_ADDED@mx.google.com>
	<dcc563d10708031242v2fee1fa8qd86fad7163970f76@mail.gmail.com>
	<20070803221508.2EF819FB37F@postgresql.org>
Comments: In-reply-to Steve Midgley <public@misuse.org>
	message dated "Fri, 03 Aug 2007 21:56:46 +0100"
Date: Fri, 03 Aug 2007 20:01:55 -0400
Message-ID: <5298.1186185715@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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