From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Midgley <public@misuse.org>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Race condition in resetting a sequence
Date: Fri, 03 Aug 2007 20:01:55 -0400
Message-ID: <5298.1186185715@sss.pgh.pa.us> (raw)
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>
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>T...
>> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, public@misuse.org, scott.marlowe@gmail.com
Subject: Re: Race condition in resetting a sequence
In-Reply-To: <5298.1186185715@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox