From: Lew <lew@lewscanon.nospam>
To: pgsql-sql@postgresql.org
Subject: Re: Race condition in resetting a sequence
Date: Sat, 04 Aug 2007 15:33:39 -0400
Message-ID: <QqidnUrDYvMOSSnbnZ2dnUVZ_uGknZ2d@comcast.com> (raw)
In-Reply-To: <5298.1186185715@sss.pgh.pa.us>
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>
<5298.1186185715@sss.pgh.pa.us>
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
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: lew@lewscanon.nospam
Subject: Re: Race condition in resetting a sequence
In-Reply-To: <QqidnUrDYvMOSSnbnZ2dnUVZ_uGknZ2d@comcast.com>
* 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