Received: from www-uk-05.digiserve.com ([195.97.231.11]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2ME4Gt65167 for ; Thu, 22 Mar 2001 09:04:16 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id OAA19811; Thu, 22 Mar 2001 14:05:08 GMT Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id D62E42A687; Thu, 22 Mar 2001 13:59:52 +0000 (GMT) Message-ID: <004201c0b2d8$5d3c8020$1001a8c0@archonet.com> From: "Richard Huxton" To: "postgresql" , "PgSQL-SQL" References: <200103221311.f2MDB0t56270@mail.postgresql.org> Subject: Re: creating "job numbers" Date: Thu, 22 Mar 2001 13:59:48 -0000 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200103/445 X-Sequence-Number: 2113 From: "postgresql" > In my current setup I have only one workstation that is actually > inputting new jobs. So, I took the expedient way to create the job > number. Ask PG to count the rows, add a magic number and insert > this data. This all happens in one connection. What are the odds of > two people hitting the db at the same time? In the current set up nil. > There is only one entry computer. I want to change the system to use > a job number generated by PG. I created a test table and I am > playing with inserting and the sequence function works great. > However, I am at a loss of how to pick up this next (last) job. I have > read the docs and I still am confused. I can not first ask with the > number will be, and asking for the previous oid after the fact can > also lead to the same problem. so that leaves me with, 1 ask for > that last oid from this workstation ip, or 2 since a job is inserted with > data, I could do a select of this data after the insert (not very elegant). I wouldn't use oid's for this - create a jobnum field and use a sequence. Sequences are smarter than you think, use: select currval('mysequence') to get the current value and select nextval('mysequence') to get the next value *for this backend* So - each client will be guaranteed a unique number. Note that if you "use up" a number and e.g. an insert fails there will be gaps in your numbering. Also check out the SERIAL data-type which can provide automatic numbering for the fields. I'm sure there are examples in Bruce's book (there's a link on www.postgresql.org) - Richard Huxton