advancing sequences
I am trying to speed up a couple of decent sized inserts (~25K rows a
pop) by using COPY FROM.
A slight snag I am running into is that the table I insert into has a
primary key generated from a sequence.
So I want to include this id in the temp text file that I copy from, so
what is the proper way to grab 25K or so values from a sequence, without
breaking anything?
In other words, if I get $current_val with "nextval('foo')" and then do
a "setval('foo', $current_val + 25000)", that creates a race condition.
The only other thing I can think of is "setval('foo', nextval('foo') +
25000)", but then I can't get the actual start value out (and I am not
even sure this would be truly atomic).
What's the proper way of doing this? Calling nextval for every row when
I create the temp file seems to somehow go against the idea of speeding
things up...
thanks,
Dmitri
On Tue, May 06, 2003 at 12:13:44 -0400,
Dmitri Bichko <dbichko@genpathpharma.com> wrote:
I am trying to speed up a couple of decent sized inserts (~25K rows a
pop) by using COPY FROM.
A slight snag I am running into is that the table I insert into has a
primary key generated from a sequence.
So I want to include this id in the temp text file that I copy from, so
what is the proper way to grab 25K or so values from a sequence, without
breaking anything?
If you don't include some columns in the copy they get the default. (I think
this might require 7.3 to do.) So if the table has a default that uses
nextval (as you would get using serial) then you should be able to just
leave the column off when doing the copy.