Re: Increment a sequence by more than one

Hi Peter,

I struggled to implement Michael's suggestion to use CACHE in this
regard when he made it but after your encouragement I've studied it
more and you and he are both totally right - CACHE is designed to do
exactly what I want. Here's the sample code so as to put this issue to
bed and to record what the solution is:

Scenario:
Bob wants a block of 50 id's
Alice just wants a single id but will accidentally "interlope" into
Bob's sequence obtainment.
"property_id_seq" = 100

Bob:
# alter sequence property_id_seq CACHE 50
Alice:
# select nextval('property_id_seq')
=> 101 (wastes ids up to 150)
Bob:
# select nextval('propery_id_seq')
=> 151 (Bob now knows that 151-201 are locked permanently for his
exclusive use)
Bob:
# alter sequence property_id_seq CACHE 1
=> Sequence will now return single ids to everyone

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's only issuing a couple of very fast sql statements..

NOTE: If all calling parties must agree to always use the 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).

I hope this helps someone else on the archives down the road. Thanks to
everyone for putting their time and attention on this problem. I'm very
grateful.

Sincerely,

Steve

At 08:00 AM 8/6/2007, Peter Childs wrote:

>On 03/08/07, Michael Glaesemann
><<mailto:grzm(at)seespotcode(dot)net>grzm(at)seespotcode(dot)net> wrote:
>
>On Aug 3, 2007, at 15:27 , Erik Jones wrote:
>
> > Is there actually a requirement that the block of 5000 values not
> > have gaps?
>
>Good point.
>
> > If not, why not make the versioned table's id column default to
> > nextval from the same sequence?
>
>Of course, the ids of the two tables could be interleaved in this
>case. This might not be an issue, of course.
>
>
>Michael Glaesemann
>grzm seespotcode net
>
>
>It seams to me that one should use the cache feature of a sequence is
>there just for this purpose.
>
>That way when you get the next value your session caches and any other
>sessions will get one after your cache range.
>
>Peter