Received: from symcom.com ([209.11.41.82]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2NFDhw09177 for ; Fri, 23 Mar 2001 10:13:45 -0500 (EST) (envelope-from pgsql@symcom.com) Message-Id: <200103231513.f2NFDhw09177@mail.postgresql.org> Received: from WorldClient [209.11.41.82] by symcom.com [209.11.41.82] with SMTP (MDaemon.v3.5.0.R) for ; Fri, 23 Mar 2001 10:21:49 -0500 Date: Fri, 23 Mar 2001 10:21:49 -0500 From: "postgresql" To: "Michael Ansley" CC: "PgSQL-SQL" Subject: RE: creating "job numbers" X-Mailer: WorldClient Pro 2.2.1 In-Reply-To: <7F124BC48D56D411812500D0B747251480F4E5@FILESERVER002> X-MDRemoteIP: 209.11.41.82 X-Return-Path: pgsql@symcom.com X-MDaemon-Deliver-To: pgsql-sql@postgresql.org X-Archive-Number: 200103/462 X-Sequence-Number: 2130 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 To: 'postgresql' 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