Re: creating "job numbers"

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(at)Yahoo(dot)com>
To: postgresql <pgsql(at)symcom(dot)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.
>