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

Jan, 

Thanks, I must be missing something here. Bear with me, I am 
trying to form an intelligent question. 

Using the serial data type... I don't understand when the backend 
would skip a number.
If the db is assigning the number with the insert, then if two (or 
more) clients are trying to insert into the db at the exact same time,  
only those that are successful should get a number. I am trying to 
envision a situation where two clients hit at the same time and 
because of problem with the insert, one aborts and the serial data 
number is skipped. I would have assumed that the aborted insert is 
just skipped no harm done.

I  guess that I could then break the insert down into two parts. Insert 
only the client name in order to grab the next job number then 
update the row. I think I should be able to reduce the number of 
aborted inserts to 1 in a couple of thousand were the abort is due to 
client input error. (I wanted to say in a million but that just seemed 
too far fetched) The only reason that I can think of that would cause 
an abort would be that data was currupted in transit to the db. Or  the 
front end crashed and sent bad data.

Is this reasonable? I feel that people with more expierence might 
shed a little light here.

Thanks for your time.

Ted


-----Original Message-----
From: Jan Wieck <JanWieck@Yahoo.com>
To: postgresql <pgsql@symcom.com>
Date: Thu, 22 Mar 2001 14:48:19 -0500 (EST)
Subject: Re: [SQL] creating "job numbers"

> 
>     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.
>