pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: postgresql <pgsql@symcom.com>
To: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
Cc: PgSQL-SQL <pgsql-sql@postgresql.org>
Subject: RE: creating "job numbers"
Date: Fri, 23 Mar 2001 10:21:49 -0500
Message-ID: <200103231513.f2NFDhw09177@mail.postgresql.org> (raw)
In-Reply-To: <7F124BC48D56D411812500D0B747251480F4E5@FILESERVER002>

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





view thread (9+ messages)  latest in thread

Message-ID: <200103231513.f2NFDhw09177@mail.postgresql.org>
Permalink:  ../200103231513.f2NFDhw09177@mail.postgresql.org/
Also on:    postgresql.org/message-id/200103231513.f2NFDhw09177@mail.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: pgsql@symcom.com, Michael.Ansley@intec-telecom-systems.com
  Subject: Re: creating "job numbers"
  In-Reply-To: <200103231513.f2NFDhw09177@mail.postgresql.org>
  References: <7F124BC48D56D411812500D0B747251480F4E5@FILESERVER002> <200103231513.f2NFDhw09177@mail.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox