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(dot)Ansley(at)intec-telecom-systems(dot)com>
To: 'postgresql' <pgsql(at)symcom(dot)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