Race condition in resetting a sequence - Mailing list pgsql-sql

Hi Scott,<br /><br /> You've moved into more general territory, so I'm starting a new thread. The code I provided to
reseta primary key sequence is actually part of Ruby on Rails core library - actually they use something very similar
towhat I originally sent:<br /><br /> SELECT setval('#{sequence}', (SELECT COALESCE(MAX(#{pk})+(SELECT increment_by
FROM#{sequence}), (SELECT min_value FROM #{sequence})) FROM #{table}), false)<br /><br /> Where:<br /> #{sequence} =
sequencename<br /> #{pk} = primary key of table under sequence<br /> #{table} = table under sequence<br /><br /> Their
codeis 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).
AlsoI think they're method is likely to be a little slower (one extra select statement) and therefore (perhaps) more
vulnerableto a race?<br /><br /> You mentioned something more general though: "As long as you're using setval you have
arace condition"? However the postgres manual states:<br /><br /><blockquote cite="" class="cite" type="cite">The
sequencefunctions, listed in <a
href="http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html#FUNCTIONS-SEQUENCE-TABLE">Table 9-34</a>,
providesimple, multiuser-safe methods for obtaining successive sequence values from sequence objects. </blockquote><br
/>(<a eudora="autourl" href="http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html">
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html</a>)<br /><br /> 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
usedin 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:<br /><br /> select setval('my_pk_seq', 500)<br /><br /> This
issueis reasonably important since Ruby on Rails is fairly widely used. As you say, the race window would be pretty
smallon a compound select -- and the Ruby function doesn't actually get called very often, but if you wouldn't mind
explaininghow the race condition would manifest, I'll post a warning on the RoR bug tracking site so that people can at
leastunderstand that there's a potential bug here..<br /><br /> Thanks again,<br /><br /> Steve<br /><br /> At 08:42 PM
8/3/2007,Scott Marlowe wrote:<br /><blockquote cite="" class="cite" type="cite">On 8/3/07, Steve Midgley
<public@misuse.org>wrote:<br /> > Hi Scott,<br /> ><br /> > Thanks for this info (and Michael too!).<br
/>><br /> > Let me see if I understand your suggestion. I would run these three<br /> > commands in
sequence:<br/> ><br /> > # select nextval('[my_seq_name]');<br /> > returns => 52 [I believe that the
sequenceis at 52]<br /> > # alter sequence [my_seq_name] increment by 5000;<br /> > # select
nextval('[my_seq_name]');<br/> > returns => 5052<br /> ><br /> > If the third command doesn't return "5052"
-i.e. it returns 5053, then<br /> > I know that somewhere in this sequence another process grabbed an id<br /> >
outfrom under me. It doesn't matter where, but I would know that my<br /> > 5000 id's are not unique and should be
discarded?If the third command<br /> > DOES return 5052, then I know that those 5000 id's are "locked" for my<br />
>use and no other application could have grabbed one of them?<br /><br /> No, that's not what would happen.  If
someonegrabbed an id after the<br /> increment value was changed, then you'd get 10052, cause they would<br />
incrementthe sequence by 5,000.since you're not using setval, and<br /> you're keeping the increment positive, there's
nodanger of collision,<br /> only of over-incrementing and leaving a giant hole in your sequence.<br /> which is ok.<br
/><br/> > Can anyone see a flaw in that? It looks right to me..<br /> ><br /> > Scott - it also seems to me
thatI need not waste all those id's if<br /> > another application does grab one during my statement: If I detect
a<br/> > failure, I could just reset the pk sequence back to the max id of the<br /> > underlying table before
tryingagain. I think this code would do it<br /> > (stolen from Ruby's postgres adaptor):<br /><br /> That is open
toa race condition.  The bad kind.<br /><br /> > SELECT setval('[seq_name]', (SELECT COALESCE(MAX([pk_of_seq]),
(SELECT<br/> > min_value FROM [seq_name])) FROM [table_of_pk]), false)<br /><br /> As long as you're using setval,
youhave a race condition.  Please<br /> avoid it.  Unless you can guarantee that no one else is using the<br />
databaseat the same time (during a maintenance window etc...)<br /><br /> > So for table "property" with pk of "id"
andsequence name<br /> > "property_id_seq":<br /> ><br /> > SELECT setval('property_id_seq', (SELECT
COALESCE(MAX(id),(SELECT<br /> > min_value FROM property_id_seq)) FROM property), false)<br /><br /> You'd think
thatthe select coalesce and the outer select setval would<br /> not have a race condition, but they still do.  Just a
muchsmaller<br /> one.<br /><br /> > I'm now starting to think that there's no way to solve this problem in<br />
>an "elegant manner" even in a stored procedure? Your method seems to be<br /> > as good as it's going to get?
(Notthat I'm complaining!)<br /><br /> Yep.  Safe is better than pretty or elegant. :)</blockquote>