Race condition in resetting a sequence

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
><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? 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(at)misuse(dot)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. :)