advancing sequences

Started by Dmitri Bichkoalmost 23 years ago2 messagesgeneral
Jump to latest
#1Dmitri Bichko
dbichko@genpathpharma.com

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

#2Bruno Wolff III
bruno@wolff.to
In reply to: Dmitri Bichko (#1)
Re: advancing sequences

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.