Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 4CB719FB397 for ; Fri, 3 Aug 2007 19:15:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 48877-10 for ; Fri, 3 Aug 2007 19:15:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from starfish.geekisp.com (mail.geekisp.com [216.168.135.169]) by postgresql.org (Postfix) with ESMTP id 2EF819FB37F for ; Fri, 3 Aug 2007 19:15:08 -0300 (ADT) Received: (qmail 20050 invoked by uid 1003); 3 Aug 2007 22:15:05 -0000 Received: from user-5440e1da.wfd80a.dsl.pol.co.uk (HELO dino-1.misuse.org) (public@misuse.org@84.64.225.218) by mail.geekisp.com with (DHE-RSA-AES256-SHA encrypted) SMTP; 3 Aug 2007 22:15:05 -0000 X-Mailer: QUALCOMM Windows Eudora Version 7.1.0.9 Date: Fri, 03 Aug 2007 21:56:46 +0100 To: "Scott Marlowe" From: Steve Midgley Subject: Race condition in resetting a sequence Cc: pgsql-sql@postgresql.org In-Reply-To: References: <20070803165058.020509FBF2F@postgresql.org> <46b381f2.0b86460a.3a8c.5460SMTPIN_ADDED@mx.google.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_578513453==.ALT" Message-Id: <20070803221508.2EF819FB37F@postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/29 X-Sequence-Number: 28967 --=====================_578513453==.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed Hi Scott, You've moved into more general territory, so I'm starting a new thread. 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) Where: #{sequence} = sequence name #{pk} = primary key of table under sequence #{table} = table under sequence Their code is a little different from what I provided before b/c it increments by one (times the increment #) above the max(pk). But essentially it's the same. (I think their method leaves small gaps in the sequence every time it runs). Also I think they're method is likely to be a little slower (one extra select statement) and therefore (perhaps) more vulnerable to a race? 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 >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? Or maybe it only has a race condition when used in ways similar to how Ruby/Rails is using it? (i.e. with a compound select/coalesce statement as one of its parameters?) Would this command have a race condition: select setval('my_pk_seq', 500) This issue is reasonably important since Ruby on Rails is fairly widely used. As you say, the race window would be pretty small on a compound select -- and the Ruby function doesn't actually get called very often, but if you wouldn't mind explaining how the race condition would manifest, I'll post a warning on the RoR bug tracking site so that people can at least understand that there's a potential bug here.. Thanks again, Steve At 08:42 PM 8/3/2007, Scott Marlowe wrote: >On 8/3/07, Steve Midgley wrote: > > Hi Scott, > > > > Thanks for this info (and Michael too!). > > > > Let me see if I understand your suggestion. I would run these three > > commands in sequence: > > > > # select nextval('[my_seq_name]'); > > returns => 52 [I believe that the sequence is at 52] > > # alter sequence [my_seq_name] increment by 5000; > > # select nextval('[my_seq_name]'); > > returns => 5052 > > > > If the third command doesn't return "5052" - i.e. it returns 5053, > then > > I know that somewhere in this sequence another process grabbed an > id > > out from under me. It doesn't matter where, but I would know that > my > > 5000 id's are not unique and should be discarded? If the third > command > > DOES return 5052, then I know that those 5000 id's are "locked" for > my > > use and no other application could have grabbed one of them? > >No, that's not what would happen. If someone grabbed an id after the >increment value was changed, then you'd get 10052, cause they would >increment the sequence by 5,000.since you're not using setval, and >you're keeping the increment positive, there's no danger of collision, >only of over-incrementing and leaving a giant hole in your sequence. >which is ok. > > > Can anyone see a flaw in that? It looks right to me.. > > > > Scott - it also seems to me that I need not waste all those id's if > > another application does grab one during my statement: If I detect > a > > failure, I could just reset the pk sequence back to the max id of > the > > underlying table before trying again. I think this code would do it > > (stolen from Ruby's postgres adaptor): > >That is open to a race condition. The bad kind. > > > SELECT setval('[seq_name]', (SELECT COALESCE(MAX([pk_of_seq]), > (SELECT > > min_value FROM [seq_name])) FROM [table_of_pk]), false) > >As long as you're using setval, you have a race condition. Please >avoid it. Unless you can guarantee that no one else is using the >database at the same time (during a maintenance window etc...) > > > So for table "property" with pk of "id" and sequence name > > "property_id_seq": > > > > SELECT setval('property_id_seq', (SELECT COALESCE(MAX(id), (SELECT > > min_value FROM property_id_seq)) FROM property), false) > >You'd think that the select coalesce and the outer select setval would >not have a race condition, but they still do. Just a much smaller >one. > > > I'm now starting to think that there's no way to solve this problem > in > > an "elegant manner" even in a stored procedure? Your method seems > to be > > as good as it's going to get? (Not that I'm complaining!) > >Yep. Safe is better than pretty or elegant. :) --=====================_578513453==.ALT Content-Type: text/html; charset="us-ascii" Hi Scott,

You've moved into more general territory, so I'm starting a new thread. 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)

Where:
#{sequence} = sequence name
#{pk} = primary key of table under sequence
#{table} = table under sequence

Their code is a little different from what I provided before b/c it increments by one (times the increment #) above the max(pk). But essentially it's the same. (I think their method leaves small gaps in the sequence every time it runs). Also I think they're method is likely to be a little slower (one extra select statement) and therefore (perhaps) more vulnerable to a race?

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 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? Or maybe it only has a race condition when used in ways similar to how Ruby/Rails is using it? (i.e. with a compound select/coalesce statement as one of its parameters?) Would this command have a race condition:

select setval('my_pk_seq', 500)

This issue is reasonably important since Ruby on Rails is fairly widely used. As you say, the race window would be pretty small on a compound select -- and the Ruby function doesn't actually get called very often, but if you wouldn't mind explaining how the race condition would manifest, I'll post a warning on the RoR bug tracking site so that people can at least understand that there's a potential bug here..

Thanks again,

Steve

At 08:42 PM 8/3/2007, Scott Marlowe wrote:
On 8/3/07, Steve Midgley <public@misuse.org> wrote:
> Hi Scott,
>
> Thanks for this info (and Michael too!).
>
> Let me see if I understand your suggestion. I would run these three
> commands in sequence:
>
> # select nextval('[my_seq_name]');
> returns => 52 [I believe that the sequence is at 52]
> # alter sequence [my_seq_name] increment by 5000;
> # select nextval('[my_seq_name]');
> returns => 5052
>
> If the third command doesn't return "5052" - i.e. it returns 5053, then
> I know that somewhere in this sequence another process grabbed an id
> out from under me. It doesn't matter where, but I would know that my
> 5000 id's are not unique and should be discarded? If the third command
> DOES return 5052, then I know that those 5000 id's are "locked" for my
> use and no other application could have grabbed one of them?

No, that's not what would happen.  If someone grabbed an id after the
increment value was changed, then you'd get 10052, cause they would
increment the sequence by 5,000.since you're not using setval, and
you're keeping the increment positive, there's no danger of collision,
only of over-incrementing and leaving a giant hole in your sequence.
which is ok.

> Can anyone see a flaw in that? It looks right to me..
>
> Scott - it also seems to me that I need not waste all those id's if
> another application does grab one during my statement: If I detect a
> failure, I could just reset the pk sequence back to the max id of the
> underlying table before trying again. I think this code would do it
> (stolen from Ruby's postgres adaptor):

That is open to a race condition.  The bad kind.

> SELECT setval('[seq_name]', (SELECT COALESCE(MAX([pk_of_seq]), (SELECT
> min_value FROM [seq_name])) FROM [table_of_pk]), false)

As long as you're using setval, you have a race condition.  Please
avoid it.  Unless you can guarantee that no one else is using the
database at the same time (during a maintenance window etc...)

> So for table "property" with pk of "id" and sequence name
> "property_id_seq":
>
> SELECT setval('property_id_seq', (SELECT COALESCE(MAX(id), (SELECT
> min_value FROM property_id_seq)) FROM property), false)

You'd think that the select coalesce and the outer select setval would
not have a race condition, but they still do.  Just a much smaller
one.

> I'm now starting to think that there's no way to solve this problem in
> an "elegant manner" even in a stored procedure? Your method seems to be
> as good as it's going to get? (Not that I'm complaining!)

Yep.  Safe is better than pretty or elegant. :)
--=====================_578513453==.ALT--