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. #
#================================================== [EMAIL PROTECTED] #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
