Thread

1. How to best grab a chunk of Ids from a sequence

   Bryan White <bryan@arcamax.com> — 2001-11-15T16:37:55Z

   I have a process that will be creating a large number of records (about
   4Million).  I am thinking of making this process run quicker by writing the
   records to a file in 'dump' format and then running that file through psql.
   
   The problem is each record has a sequence number and I need to know the
   sequence ids for other
   purposes in this process.
   
   My question is is there a way to grab 4 million IDs from a sequence without
   calling nextval once for each ID.  Note, this sequence is being actively
   drawn on by other processes.
   
   ---------
   Bryan White
   
   
   
   

2. Re: How to best grab a chunk of Ids from a sequence

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-11-15T18:25:24Z

   "Bryan White" <bryan@arcamax.com> writes:
   > My question is is there a way to grab 4 million IDs from a sequence without
   > calling nextval once for each ID.  Note, this sequence is being actively
   > drawn on by other processes.
   
   I can't think of any really clean way, but you could do something like
   
   	select setval('seq', nextval('seq') + 4000000 + 100);
   
   and then use the 4m IDs just before the returned value.  Because the
   nextval and setval aren't an atomic operation, it's possible that
   some other processes will get in and do nextval in between.  The extra
   100 advance of the sequence should provide an adequate buffer for that
   ... but of course it could theoretically be insufficient...
   
   			regards, tom lane
   
   

3. Re: How to best grab a chunk of Ids from a sequence

   Jason Earl <jason.earl@simplot.com> — 2001-11-15T19:05:19Z

   What you could do is use the setval() function to set the value of the
   sequence to whatever it is now + 4 million.  That would give you a
   great big hole in your sequence from which you could draw sequence
   values from.  Your processes that still needed to use the sequence
   would still be able to, and the information that you are importing
   could use the sequence numbers that you skipped.
   
   Jason
   
   "Bryan White" <bryan@arcamax.com> writes:
   
   > I have a process that will be creating a large number of records (about
   > 4Million).  I am thinking of making this process run quicker by writing the
   > records to a file in 'dump' format and then running that file through psql.
   > 
   > The problem is each record has a sequence number and I need to know the
   > sequence ids for other
   > purposes in this process.
   > 
   > My question is is there a way to grab 4 million IDs from a sequence without
   > calling nextval once for each ID.  Note, this sequence is being actively
   > drawn on by other processes.
   > 
   > ---------
   > Bryan White
   > 
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org