Re: [SQL] Increment a sequence by more than one
On 03/08/07, Michael Glaesemann <[EMAIL PROTECTED]> 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
Re: [SQL] 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
<[EMAIL PROTECTED]> 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
Re: [SQL] Best Fit SQL query statement
On Aug 6, 3:57 am, Kiran <[EMAIL PROTECTED]> wrote: > and if I query on 98456 the result must be 98456, > However if I query on 98455 the result must be 9845 > and If I query 9849 the result must be 984 SELECT MAX(t1) FROM t1 WHERE '9849' LIKE t1 || '%'; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Using function like where clause
Hello, I have 2 questions. 1) Can I use a function that will return a string in a where clause like bellow? select * from table where my_function_making_where() and another_field = 'another_think' 2) Can I use a function that will return a string to return the list of columns that I want to show like below? select my_function_making_list_of_columns() from table where field_test = 'mydatum' Thanks ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Using function like where clause
On Aug 6, 2007, at 14:44 , Ranieri Mazili wrote: 1) Can I use a function that will return a string in a where clause like bellow? select * from table where my_function_making_where() and another_field = 'another_think' Probably. What have you tried? What does the documentation say? If you're having trouble with a specific function, please provide the code and hopefully we can work it out. 2) Can I use a function that will return a string to return the list of columns that I want to show like below? select my_function_making_list_of_columns() from table where field_test = 'mydatum' Probably not. What have you tried? What does the documentation say? A couple of minutes in psql would probably be faster than sending an email to the list. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
