pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIncrement a sequence by more than one
11+ messages / 5 participants
[nested] [flat]
* Increment a sequence by more than one
@ 2007-08-03 16:50 Steve Midgley <public@misuse.org>
0 siblings, 3 replies; 11+ messages in thread
From: Steve Midgley @ 2007-08-03 16:50 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm writing an import app in a third party language. It's going to use
"copy to" to move data from STDIN to a postgres (8.2) table. There are
some complexities though: it's going to copy the records to a
"versioned" table first, and then at a later time the records will be
copied by a different procedure to the "live" table. The live table and
versioned table are identical in terms of their field definitions. But
there is no sequence associated with the versioned table (whose primary
key is "id" plus "import_group_id", whereas the live table's pk is just
"id"). So all versioned table entries must already "know" what their id
would be in the live table. (This makes sense for other business
process we have, but it's a bit of a problem in this instance).
My problem: I'd like to be able to grab a block of id's from the live
table's pk sequence. So let's say my importer has 5,000 new rows to
import and the current max pk in the live table is 540,203. I'd like to
be able to increment the primary key sequence in such a way that I get
a block of ids all to myself and the sequence is reset to 545,203 with
a guarantee that all the id's between 540203 and 545203 are unused.
I'm guessing this can be done with a stored procedure, but if possible
I'd like to be able to run this command from my third party app without
calling a stored procedure (we try not to use stored procedures here
b/c we code entirely in this third party language - if we had to, it's
possible we could install a stored procedure though).
But since I've seen so much magic on display from people on this list,
I'm going to ask if it's possible to do this solely from PG SQL sent
from a third party language? The main tricky bit seems to be ensuring
that everything is locked so two such increment calls at the same time
don't yield overlapping blocks of ids. Is there a way to "lock" the
sequence generator for the duration of a "nextval" and "setval" call?
Since pk sequence functions like nextval cannot be rolled back, I'm
guessing that "begin/end" won't do the job?
I.e:
-- need "magic lock" statement on pk sequence here
nextval
-- returns 540203
setval(545203)
-- now sequence is set to where I want it and I "own" 5000 id's
-- release magic lock here
My fallback is to just have a routine that calls "nextval" a bunch of
times and stores all the id's it gets - they may or may not be
sequential but they'll be unique. This is going to be a really slow way
to get a large number of id's of course and just seems plain
wrongheaded in many ways.
Any insights? All help is appreciated and input on a better way to
solve the problem completely is of course welcome as well.
Sincerely,
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-03 17:26 Michael Glaesemann <grzm@seespotcode.net>
parent: Steve Midgley <public@misuse.org>
2 siblings, 0 replies; 11+ messages in thread
From: Michael Glaesemann @ 2007-08-03 17:26 UTC (permalink / raw)
To: Steve Midgley <public@misuse.org>; +Cc: pgsql-sql
On Aug 3, 2007, at 11:50 , Steve Midgley wrote:
> My problem: I'd like to be able to grab a block of id's from the
> live table's pk sequence. So let's say my importer has 5,000 new
> rows to import and the current max pk in the live table is 540,203.
> I'd like to be able to increment the primary key sequence in such a
> way that I get a block of ids all to myself and the sequence is
> reset to 545,203 with a guarantee that all the id's between 540203
> and 545203 are unused.
Setting the next number that will be taken is generated is
straightforward.
ALTER SEQUENCE foo_seq RESTART WITH 545203;
Perhaps doing something like (untested):
ALTER SEQUENCE foo_seq RESTART WITH (SELECT last_value + CASE WHEN
is_called THEN 1 ELSE 0 END);
You'll need to manage the skipped values yourself though, of course.
Perhaps set up a table to hold the current number used in the skipped
range. Basically this would be the same approach as that used by
people who need to guarantee gapless sequences: you can check the
archives for details, but basically you need to make sure the table
is properly locked when you're planning to use a new number.
> But since I've seen so much magic on display from people on this
> list, I'm going to ask if it's possible to do this solely from PG
> SQL sent from a third party language? The main tricky bit seems to
> be ensuring that everything is locked so two such increment calls
> at the same time don't yield overlapping blocks of ids. Is there a
> way to "lock" the sequence generator for the duration of a
> "nextval" and "setval" call? Since pk sequence functions like
> nextval cannot be rolled back, I'm guessing that "begin/end" won't
> do the job?
I'm not sure, but perhaps calling SELECT FOR UPDATE on the sequence
itself (i.e., not using nextval/setval) would give the appropriate
lock, though I wouldn't be surprised if it isn't possible. As you've
noted, sequences are designed for performance to ignore transactions,
so this may not be possible (though perhaps that's just values
returned via the nextval function).
Hope this gives you some additional ideas on how to handle this.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-03 19:01 Scott Marlowe <scott.marlowe@gmail.com>
parent: Steve Midgley <public@misuse.org>
2 siblings, 3 replies; 11+ messages in thread
From: Scott Marlowe @ 2007-08-03 19:01 UTC (permalink / raw)
To: Steve Midgley <public@misuse.org>; +Cc: pgsql-sql
On 8/3/07, Steve Midgley <public@misuse.org> wrote:
>
> Hi,
>
> I'm writing an import app in a third party language. It's going to use
> "copy to" to move data from STDIN to a postgres (8.2) table. There are some
> complexities though: it's going to copy the records to a "versioned" table
> first, and then at a later time the records will be copied by a different
> procedure to the "live" table. The live table and versioned table are
> identical in terms of their field definitions. But there is no sequence
> associated with the versioned table (whose primary key is "id" plus
> "import_group_id", whereas the live table's pk is just "id"). So all
> versioned table entries must already "know" what their id would be in the
> live table. (This makes sense for other business process we have, but it's a
> bit of a problem in this instance).
>
> My problem: I'd like to be able to grab a block of id's from the live
> table's pk sequence. So let's say my importer has 5,000 new rows to import
> and the current max pk in the live table is 540,203. I'd like to be able to
> increment the primary key sequence in such a way that I get a block of ids
> all to myself and the sequence is reset to 545,203 with a guarantee that all
> the id's between 540203 and 545203 are unused.
The real danger in doing this is race conditions. Most anything you
do involves a possible race condition. As long as the race condition
doesn't result in an id getting used twice, you're safe.
So:
test=# create sequence m;
CREATE SEQUENCE
test=# select nextval('m');
nextval
---------
1
(1 row)
test=# alter sequence m increment by 5000;
ALTER SEQUENCE
test=# select nextval('m');
nextval
---------
5001
(1 row)
test=# alter sequence m increment by 1;
ALTER SEQUENCE
test=# select nextval('m');
nextval
---------
5002
(1 row)
In this method, the only possible race condition is that someone might
run a nextval('m') between the time you set the increment to 5000 and
1 again. If that happens, you'd have 5,000 missing ids, but since
sequences are designed to prevent dupes, not holes, that's ok.
> But since I've seen so much magic on display from people on this list, I'm
> going to ask if it's possible to do this solely from PG SQL sent from a
> third party language? The main tricky bit seems to be ensuring that
> everything is locked so two such increment calls at the same time don't
> yield overlapping blocks of ids. Is there a way to "lock" the sequence
> generator for the duration of a "nextval" and "setval" call?
Avoiding the setval is the real key. It doesn't scale. Missing 5,000
ids is no big deal. repeating them IS a big deal. Not using setval
is the best answer.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-03 19:28 Steve Midgley <public@misuse.org>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Steve Midgley @ 2007-08-03 19:28 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
Hi Scott,
Thanks for this info (and Michael too!).
Let me see if I understand your suggestion. I would run these three
commands in sequence:
# select nextval('[my_seq_name]');
returns => 52 [I believe that the sequence is at 52]
# alter sequence [my_seq_name] increment by 5000;
# select nextval('[my_seq_name]');
returns => 5052
If the third command doesn't return "5052" - i.e. it returns 5053, then
I know that somewhere in this sequence another process grabbed an id
out from under me. It doesn't matter where, but I would know that my
5000 id's are not unique and should be discarded? If the third command
DOES return 5052, then I know that those 5000 id's are "locked" for my
use and no other application could have grabbed one of them?
Can anyone see a flaw in that? It looks right to me..
Scott - it also seems to me that I need not waste all those id's if
another application does grab one during my statement: If I detect a
failure, I could just reset the pk sequence back to the max id of the
underlying table before trying again. I think this code would do it
(stolen from Ruby's postgres adaptor):
SELECT setval('[seq_name]', (SELECT COALESCE(MAX([pk_of_seq]), (SELECT
min_value FROM [seq_name])) FROM [table_of_pk]), false)
So for table "property" with pk of "id" and sequence name
"property_id_seq":
SELECT setval('property_id_seq', (SELECT COALESCE(MAX(id), (SELECT
min_value FROM property_id_seq)) FROM property), false)
Then I could just retry the first process - though on a table that is
very busy, I might have retry for a while..
Regarding Michael's suggestion - I tried messing around with LOCK and
similar commands but they're only allowed to run against TABLES not
SEQUENCES - too bad - that would have been perfect.
I'm now starting to think that there's no way to solve this problem in
an "elegant manner" even in a stored procedure? Your method seems to be
as good as it's going to get? (Not that I'm complaining!)
Thanks again - any thoughts are welcome,
Steve
At 08:01 PM 8/3/2007, Scott Marlowe wrote:
>On 8/3/07, Steve Midgley <public@misuse.org> wrote:
> >
> > Hi,
> >
> > I'm writing an import app in a third party language. It's going to
> use
> > "copy to" to move data from STDIN to a postgres (8.2) table. There
> are some
> > complexities though: it's going to copy the records to a
> "versioned" table
> > first, and then at a later time the records will be copied by a
> different
> > procedure to the "live" table. The live table and versioned table
> are
> > identical in terms of their field definitions. But there is no
> sequence
> > associated with the versioned table (whose primary key is "id" plus
> > "import_group_id", whereas the live table's pk is just "id"). So
> all
> > versioned table entries must already "know" what their id would be
> in the
> > live table. (This makes sense for other business process we have,
> but it's a
> > bit of a problem in this instance).
> >
> > My problem: I'd like to be able to grab a block of id's from the
> live
> > table's pk sequence. So let's say my importer has 5,000 new rows to
> import
> > and the current max pk in the live table is 540,203. I'd like to be
> able to
> > increment the primary key sequence in such a way that I get a block
> of ids
> > all to myself and the sequence is reset to 545,203 with a guarantee
> that all
> > the id's between 540203 and 545203 are unused.
>
>The real danger in doing this is race conditions. Most anything you
>do involves a possible race condition. As long as the race condition
>doesn't result in an id getting used twice, you're safe.
>
>So:
>
>test=# create sequence m;
>CREATE SEQUENCE
>test=# select nextval('m');
> nextval
>---------
> 1
>(1 row)
>
>test=# alter sequence m increment by 5000;
>ALTER SEQUENCE
>test=# select nextval('m');
> nextval
>---------
> 5001
>(1 row)
>
>test=# alter sequence m increment by 1;
>ALTER SEQUENCE
>test=# select nextval('m');
> nextval
>---------
> 5002
>(1 row)
>
>In this method, the only possible race condition is that someone might
>run a nextval('m') between the time you set the increment to 5000 and
>1 again. If that happens, you'd have 5,000 missing ids, but since
>sequences are designed to prevent dupes, not holes, that's ok.
>
> > But since I've seen so much magic on display from people on this
> list, I'm
> > going to ask if it's possible to do this solely from PG SQL sent
> from a
> > third party language? The main tricky bit seems to be ensuring that
> > everything is locked so two such increment calls at the same time
> don't
> > yield overlapping blocks of ids. Is there a way to "lock" the
> sequence
> > generator for the duration of a "nextval" and "setval" call?
>
>Avoiding the setval is the real key. It doesn't scale. Missing 5,000
>ids is no big deal. repeating them IS a big deal. Not using setval
>is the best answer.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-03 19:42 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2007-08-03 19:42 UTC (permalink / raw)
To: Steve Midgley <public@misuse.org>; +Cc: pgsql-sql
On 8/3/07, Steve Midgley <public@misuse.org> wrote:
> Hi Scott,
>
> Thanks for this info (and Michael too!).
>
> Let me see if I understand your suggestion. I would run these three
> commands in sequence:
>
> # select nextval('[my_seq_name]');
> returns => 52 [I believe that the sequence is at 52]
> # alter sequence [my_seq_name] increment by 5000;
> # select nextval('[my_seq_name]');
> returns => 5052
>
> If the third command doesn't return "5052" - i.e. it returns 5053, then
> I know that somewhere in this sequence another process grabbed an id
> out from under me. It doesn't matter where, but I would know that my
> 5000 id's are not unique and should be discarded? If the third command
> DOES return 5052, then I know that those 5000 id's are "locked" for my
> use and no other application could have grabbed one of them?
No, that's not what would happen. If someone grabbed an id after the
increment value was changed, then you'd get 10052, cause they would
increment the sequence by 5,000.since you're not using setval, and
you're keeping the increment positive, there's no danger of collision,
only of over-incrementing and leaving a giant hole in your sequence.
which is ok.
> Can anyone see a flaw in that? It looks right to me..
>
> Scott - it also seems to me that I need not waste all those id's if
> another application does grab one during my statement: If I detect a
> failure, I could just reset the pk sequence back to the max id of the
> underlying table before trying again. I think this code would do it
> (stolen from Ruby's postgres adaptor):
That is open to a race condition. The bad kind.
> SELECT setval('[seq_name]', (SELECT COALESCE(MAX([pk_of_seq]), (SELECT
> min_value FROM [seq_name])) FROM [table_of_pk]), false)
As long as you're using setval, you have a race condition. Please
avoid it. Unless you can guarantee that no one else is using the
database at the same time (during a maintenance window etc...)
> So for table "property" with pk of "id" and sequence name
> "property_id_seq":
>
> SELECT setval('property_id_seq', (SELECT COALESCE(MAX(id), (SELECT
> min_value FROM property_id_seq)) FROM property), false)
You'd think that the select coalesce and the outer select setval would
not have a race condition, but they still do. Just a much smaller
one.
> I'm now starting to think that there's no way to solve this problem in
> an "elegant manner" even in a stored procedure? Your method seems to be
> as good as it's going to get? (Not that I'm complaining!)
Yep. Safe is better than pretty or elegant. :)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-03 19:45 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2007-08-03 19:45 UTC (permalink / raw)
To: Steve Midgley <public@misuse.org>; +Cc: pgsql-sql
Oh, another point. You should run the
alter sequence m increment 5000;
select nextval('m');
alter sequence m increment 1;
one right after the other to reduce the number of 5000 wide holes in
your sequence.
Or, given the size of bigint, you could just set the increment to 5000
and leave it there, and then any insert could grab nextval('m') and
insert up to 5000 more ids with monotonically increasing ids safely.
Note you wouldn't use defaults or nextvals for the rest, you'd have to
calculate them in your application.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-03 20:27 Erik Jones <erik@myemma.com>
parent: Steve Midgley <public@misuse.org>
2 siblings, 1 reply; 11+ messages in thread
From: Erik Jones @ 2007-08-03 20:27 UTC (permalink / raw)
To: Steve Midgley <public@misuse.org>; +Cc: pgsql-sql
On Aug 3, 2007, at 11:50 AM, Steve Midgley wrote:
> Hi,
>
> I'm writing an import app in a third party language. It's going to
> use "copy to" to move data from STDIN to a postgres (8.2) table.
> There are some complexities though: it's going to copy the records
> to a "versioned" table first, and then at a later time the records
> will be copied by a different procedure to the "live" table. The
> live table and versioned table are identical in terms of their
> field definitions. But there is no sequence associated with the
> versioned table (whose primary key is "id" plus "import_group_id",
> whereas the live table's pk is just "id"). So all versioned table
> entries must already "know" what their id would be in the live
> table. (This makes sense for other business process we have, but
> it's a bit of a problem in this instance).
>
> My problem: I'd like to be able to grab a block of id's from the
> live table's pk sequence. So let's say my importer has 5,000 new
> rows to import and the current max pk in the live table is 540,203.
> I'd like to be able to increment the primary key sequence in such a
> way that I get a block of ids all to myself and the sequence is
> reset to 545,203 with a guarantee that all the id's between 540203
> and 545203 are unused.
>
> I'm guessing this can be done with a stored procedure, but if
> possible I'd like to be able to run this command from my third
> party app without calling a stored procedure (we try not to use
> stored procedures here b/c we code entirely in this third party
> language - if we had to, it's possible we could install a stored
> procedure though).
>
> But since I've seen so much magic on display from people on this
> list, I'm going to ask if it's possible to do this solely from PG
> SQL sent from a third party language? The main tricky bit seems to
> be ensuring that everything is locked so two such increment calls
> at the same time don't yield overlapping blocks of ids. Is there a
> way to "lock" the sequence generator for the duration of a
> "nextval" and "setval" call? Since pk sequence functions like
> nextval cannot be rolled back, I'm guessing that "begin/end" won't
> do the job?
>
> I.e:
>
> -- need "magic lock" statement on pk sequence here
> nextval
> -- returns 540203
> setval(545203)
> -- now sequence is set to where I want it and I "own" 5000 id's
> -- release magic lock here
>
> My fallback is to just have a routine that calls "nextval" a bunch
> of times and stores all the id's it gets - they may or may not be
> sequential but they'll be unique. This is going to be a really slow
> way to get a large number of id's of course and just seems plain
> wrongheaded in many ways.
>
> Any insights? All help is appreciated and input on a better way to
> solve the problem completely is of course welcome as well.
Is there actually a requirement that the block of 5000 values not
have gaps? If not, why not make the versioned table's id column
default to nextval from the same sequence? Then when the data is
copied over to the live table, as long as you supply the the id it
won't generate a new id and you'll maintain your row-row
relationships. If you do require that the block not have gaps, check
out the article on how to do this here: http://www.varlena.com/
varlena/GeneralBits/130.php
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-03 20:31 Michael Glaesemann <grzm@seespotcode.net>
parent: Steve Midgley <public@misuse.org>
0 siblings, 0 replies; 11+ messages in thread
From: Michael Glaesemann @ 2007-08-03 20:31 UTC (permalink / raw)
To: Steve Midgley <public@misuse.org>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
On Aug 3, 2007, at 14:28 , Steve Midgley wrote:
AIUI, one difference between the solutions Scott and I proposed is
that while INCREMENT is set at 5000, each time nextval is called the
sequence is incremented by 5000. For example:
test=# select nextval('foos_foo_id_seq');
nextval
---------
1
(1 row)
test=# select nextval('foos_foo_id_seq');
nextval
---------
2
(1 row)
test=# alter sequence foos_foo_id_seq increment 5000;
ALTER SEQUENCE
test=# select nextval('foos_foo_id_seq');
nextval
---------
5002
(1 row)
test=# select nextval('foos_foo_id_seq');
nextval
---------
10002
(1 row)
The only issue with this is that it burns through sequence values
faster. That may not be a concern, of course.
I wonder if there isn't a way to use ALTER SEQUENCE ... CACHE to
handle this:
-- Alice's session
test=# select nextval('foos_foo_id_seq');
nextval
---------
15002
(1 row)
-- Bob's session
test=# select nextval('foos_foo_id_seq');
nextval
---------
15003
(1 row)
test=# select nextval('foos_foo_id_seq');
nextval
---------
15004
(1 row)
-- Alice's session
test=# alter sequence foos_foo_id_seq cache 5000;
ALTER SEQUENCE
test=# select nextval('foos_foo_id_seq'); nextval
---------
20003
(1 row)
test=# select nextval('foos_foo_id_seq');
nextval
---------
20004
(1 row)
Now you should be able to safely use the values from nextval to
nextval + cache without worrying that the values in that range are
going to be used by another backend. It looks like you could even do:
-- Alice's session
test=# select nextval('foos_foo_id_seq');
nextval
---------
30096
(1 row)
test=# alter sequence foos_foo_id_seq cache 10; -- set cache to
preallocate
ALTER SEQUENCE
test=# select nextval('foos_foo_id_seq'); -- get nextval and
preallocate the next 10
nextval
---------
30097
(1 row)
-- Bob's session
test=# select nextval('foos_foo_id_seq');
nextval
---------
30107
(1 row)
-- Alice's session
test=# alter sequence foos_foo_id_seq cache 1; -- reset cache so
other backends aren't burning sequence values unnecessarily
ALTER SEQUENCE
test=# select nextval('foos_foo_id_seq'); -- note that the previously
cached values (for both Alice and Bob's session) are discarded, and
available for manual entry
nextval
---------
30117
(1 row)
Again, you can set up another table to keep track of the values that
are going to be used manually, perhaps something like:
CREATE TABLE preallocated_foo_ids
(
current_value INTEGER NOT NULL
, maximum_value INTEGER NOT NULL
, check (current_value <= maximum_value)
);
Then, to use:
test=# ALTER SEQUENCE foos_foo_id_seq CACHE 10;
ALTER SEQUENCE
test=# DELETE FROM preallocated_foo_ids; -- clear old ones
DELETE 1
test=# INSERT INTO preallocated_foo_ids (current_value, maximum_value)
test-# SELECT val, val + 10
test-# FROM (SELECT nextval('foos_foo_id_seq')) AS seq(val);
INSERT 0 1
test=# ALTER SEQUENCE foos_foo_id_seq CACHE 1;
ALTER SEQUENCE
test=# SELECT * FROM preallocated_foo_ids;
current_value | maximum_value
---------------+---------------
30142 | 30152
(1 row)
You've now got a preallocated range in preallocated_ids. Just use
standard table locking on preallocated_foo_ids: there shouldn't be
heavy contention on this table during your load, so I don't think
performance should suffer too badly.
test=# BEGIN;
BEGIN
test=# SELECT current_value
test-# FROM preallocated_foo_ids
test-# FOR UPDATE;
current_value
---------------
30142
(1 row)
test=# INSERT INTO foos (foo_id) VALUES (30142);
INSERT 0 1
test=# UPDATE preallocated_foo_ids
test-# SET current_value = current_value + 1;
UPDATE 1
test=# COMMIT;
COMMIT
test=# SELECT * FROM preallocated_foo_ids;
current_value | maximum_value
---------------+---------------
30143 | 30152
(1 row)
When you run into an error because of the CHECK constraint, you know
you've hit the end of your range (if you haven't been checking
otherwise).
> Regarding Michael's suggestion - I tried messing around with LOCK
> and similar commands but they're only allowed to run against TABLES
> not SEQUENCES - too bad - that would have been perfect.
Yeah, I thought that might be the case.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-03 20:33 Michael Glaesemann <grzm@seespotcode.net>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Glaesemann @ 2007-08-03 20:33 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Steve Midgley <public@misuse.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-06 07:00 Peter Childs <peterachilds@gmail.com>
parent: Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 11+ messages in thread
From: Peter Childs @ 2007-08-06 07:00 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Erik Jones <erik@myemma.com>; Steve Midgley <public@misuse.org>; pgsql-sql
On 03/08/07, Michael Glaesemann <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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Increment a sequence by more than one
@ 2007-08-06 12:20 Steve Midgley <public@misuse.org>
parent: Peter Childs <peterachilds@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Steve Midgley @ 2007-08-06 12:20 UTC (permalink / raw)
To: Peter Childs <peterachilds@gmail.com>; Michael Glaesemann <grzm@seespotcode.net>; +Cc: Erik Jones <erik@myemma.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-08-06 12:20 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-03 16:50 Increment a sequence by more than one Steve Midgley <public@misuse.org>
2007-08-03 17:26 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-03 19:01 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-08-03 19:28 ` Steve Midgley <public@misuse.org>
2007-08-03 20:31 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-03 19:42 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-08-03 19:45 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-08-03 20:27 ` Erik Jones <erik@myemma.com>
2007-08-03 20:33 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-06 07:00 ` Peter Childs <peterachilds@gmail.com>
2007-08-06 12:20 ` Steve Midgley <public@misuse.org>
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