Re: Increment a sequence by more than one

Hi Peter,<br /><br /> I struggled to implement Michael's suggestion to use CACHE in this regard when he made it but
afteryour encouragement I've studied it more and you and he are both totally right - CACHE is designed to do exactly
whatI want. Here's the sample code so as to put this issue to bed and to record what the solution is:<br /><br />
Scenario:<br/> Bob wants a block of 50 id's<br /> Alice just wants a single id but will accidentally "interlope" into
Bob'ssequence obtainment.<br /> "property_id_seq" = 100<br /><br /> Bob:<br /> # alter sequence property_id_seq CACHE
50<br/> Alice:<br /> # select nextval('property_id_seq')<br /> => 101 (wastes ids up to 150)<br /> Bob:<br /> #
selectnextval('propery_id_seq')<br /> => 151 (Bob now knows that 151-201 are locked permanently for his exclusive
use)<br/> Bob:<br /> # alter sequence property_id_seq CACHE 1<br /> => Sequence will now return single ids to
everyone<br/><br /> So in the worst case, there will be id "wastage" equal to the CACHE size times the number of
"interlopers"who grab ids while Bob is obtaining his block. And Bob's time to grab a set of id's is fairly small since
he'sonly issuing a couple of very fast sql statements..<br /><br /> NOTE: If all calling parties must agree to always
usethe same CACHE number for obtaining blocks of id's, then this method seems bulletproof (if two parties use differing
CACHE#'s then they could cause too few id's to be CACHED to one of the parties).<br /><br /> I hope this helps someone
elseon the archives down the road. Thanks to everyone for putting their time and attention on this problem. I'm very
grateful.<br/><br /> Sincerely,<br /><br /> Steve<br /><br /> At 08:00 AM 8/6/2007, Peter Childs wrote:<br /><br /><br
/><blockquotecite="" class="cite" type="cite">On 03/08/07, <b>Michael Glaesemann</b> <<a
href="mailto:grzm@seespotcode.net">grzm@seespotcode.net</a>>wrote:<br /><dl><br /><dd>On Aug 3, 2007, at 15:27 ,
ErikJones wrote:<br /><br /><dd>> Is there actually a requirement that the block of 5000 values not<br /><dd>>
havegaps?<br /><br /><dd>Good point.<br /><br /><dd>> If not, why not make the versioned table's id column default
to<br /><dd>> nextval from the same sequence?<br /><br /><dd>Of course, the ids of the two tables could be
interleavedin this<br /><dd>case. This might not be an issue, of course.<br /><br /><br /><dd>Michael Glaesemann<br
/><dd>grzmseespotcode net<br /><br /></dl><br /> It seams to me that one should use the cache feature of a sequence is
therejust for this purpose.<br /><br /> That way when you get the next value your session caches and any other sessions
willget one after your cache range. <br /><br /> Peter</blockquote>