Received: from smtp013.mail.yahoo.com (smtp013.mail.yahoo.com [216.136.173.57]) by mail.postgresql.org (8.11.3/8.11.1) with SMTP id f2MKhNw14728 for ; Thu, 22 Mar 2001 15:43:23 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from pd957511c.dip.t-dialin.net (HELO jupiter.jw.home) (217.87.81.28) by smtp.mail.vip.sc5.yahoo.com with SMTP; 22 Mar 2001 19:43:20 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id OAA28442; Thu, 22 Mar 2001 14:48:19 -0500 From: Jan Wieck Message-Id: <200103221948.OAA28442@jupiter.jw.home> Subject: Re: creating "job numbers" In-Reply-To: <200103221311.f2MDB0t56270@mail.postgresql.org> from postgresql at "Mar 22, 2001 08:19:03 am" To: postgresql Date: Thu, 22 Mar 2001 14:48:19 -0500 (EST) CC: PgSQL-SQL X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/454 X-Sequence-Number: 2122 postgresql wrote: > How are you professionals handling this problem? I like the ability to > insert and have the system give me the number. As I grow into more > workstations inputting the jobs I won't have to worry about chasing > the next highest number. Two possible ways: 1. If you can live with gaps in the job numbers, you can use the serial data type. That is, you create your table like CREATE TABLE jobs ( job_id serial PRIMARY KEY, employee_id integer REFERENCES staff, ... ); Now your application can INSERT a row not specifying an explicit value for the job_id like INSERT INTO jobs (employee_id, ...) VALUES (4711, ...); and reading the PostgreSQL assigned job_id back with SELECT currval('jobs_job_id_seq'); Even if there are other processes doing the same concurrently, the assigned job_id is guaranteed to be unique and the currval() given back by your database connection isn't affected by it. 2. If you cannot live with gaps in the numbers, have a separate table containing counters like CREATE TABLE app_counter ( count_name text PRIMARY KEY, count_val integer; ); INSERT INTO app_counter VALUES ('job_id', 0); Now it's the duty of your application to use transactions and do mainly the following: BEGIN TRANSACTION; UPDATE app_counter set count_val = count_val + 1 WHERE count_name = 'job_id'; INSERT INTO jobs SELECT count_val, 4711, ... FROM app_counter WHERE count_name = 'job_id'; SELECT count_val FROM app_counter WHERE count_name = 'job_id'; COMMIT TRANSACTION; For method 1, transaction aborts can cause missing job_id's because sequence numbers aren't rolled back. OTOH method 2 will lock the table app_counter at the UPDATE and release the lock at COMMIT. So it'll have a little less throughput than method 1, but if you really get a performance problem with creating job's in the database, your company must be gushing cash and there should be plenty of money for some bigger boxes :-). Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com