Обсуждение: 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.