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 <[EMAIL PROTECTED]> 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. :)
