RE: creating "job numbers"

Thanks Micheal,

I guess what I am trying to figure out is, 'how important is this in 
reality, in a working setup'. I know that at times I want things to be 
perfect, and it is not necessary. 

I was thinking about how I do this manually. 

I would take a sheet of paper, write numbers down the left column. 
make a couple of sheets with increasing numbers. As jobs enter the  
shop, I populate the meta data. If a job gets killed, it is marked as 
dead but the number does not go away. It could be used again.

What I really need to do is:

Insert "blank" into openjobs; 
SELECT currval('jobs_job_id_seq');
UPDATE openjobs "job meta data" where jobno= "returned number 
from prev select"

Even if this job fails, I have the empty row. I can choose to put a job 
in there later if I want. Or leave it blank. I feel that all the numbers 
have to be accounted for as used or null. But it should not skip.


Thanks,

Ted


-----Original Message-----
From: Michael Ansley 
<Michael.Ansley@intec-telecom-systems.com>
To: 'postgresql' <pgsql@symcom.com>
Date: Fri, 23 Mar 2001 13:22:09 -0000
Subject: RE: [SQL] creating "job numbers"

> The procedure is something more like this:
> 
> The first backend grabs the sequence number, say 1, and tries to
> insert.
> Whether or not this insert succeeds, the number 1 is gone from 
the
> sequence.
> Then backend two tries to insert, and grabs the number 2 from the
> sequence.
> After this, the first backend rolls back, and doesn't insert.  The next
> backend will get number 3.  And so number 1 is lost.  If the 
session is
> caching sequence number, then even more numbers may be lost.  
Anyway,
> the
> principle is that sequences do not roll back.  Once you have a 
number,
> it's
> gone, whether or not you use it.  This is because keeping track of
> numbers
> to keep them contiguous is a time-consuming exercise, and 
causes
> locking
> problems.  So, the principle is that a sequence will always give you 
a
> distinct number, but not necessarily the next number.
> 
> Hope this helps...
> 
> 
> MikeA