Re: creating "job numbers"

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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com