Re: creating "job numbers" - Mailing list pgsql-sql

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
tablelike
 
           CREATE TABLE jobs (               job_id      serial PRIMARY KEY,               employee_id integer
REFERENCESstaff,               ...           );
 
       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
guaranteedto 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
companymust 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