From: Steve Midgley <public@misuse.org>
To: Peter Childs <peterachilds@gmail.com>
To: Michael Glaesemann <grzm@seespotcode.net>
Cc: Erik Jones <erik@myemma.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Increment a sequence by more than one
Date: Mon, 06 Aug 2007 13:20:05 +0100
Message-ID: <20070806122108.307F59FB927@postgresql.org> (raw)
In-Reply-To: <a2de01dd0708060000k504525bdt99177726bb7af529@mail.gmail.co m>
References: <20070803165058.020509FBF2F@postgresql.org>
<BA0AEA3C-A9C1-462E-9F86-34755121673D@myemma.com>
<6137A8C5-187D-46E8-B9C2-822D05C5DFC2@seespotcode.net>
<a2de01dd0708060000k504525bdt99177726bb7af529@mail.gmail.com>
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@seespotcode.net>grzm@seespotcode.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: public@misuse.org, peterachilds@gmail.com, grzm@seespotcode.net, erik@myemma.com
Subject: Re: Increment a sequence by more than one
In-Reply-To: <20070806122108.307F59FB927@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox