From: Ash Grove <ash_grv7@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: concurrency problem
Date: Fri, 16 Jun 2006 07:08:55 -0700 (PDT)
Message-ID: <20060616140855.72700.qmail@web52513.mail.yahoo.com> (raw)
In-Reply-To: <ee0ad3a30606152114g122d0021ja7c603a8f36ae62c@mail.gmail.com>
>INSERT INTO rcp_patient_visit_monitor (
> entry_no, patient_id, visit_date,
> is_newpatient,
> visit_type, is_medical,
> is_review, is_labtest, is_scan,
> is_scopy, is_xray,
> weight, height)
> VALUES ((SELECT
> coalesce(max(entry_no)+1, 1) FROM
>
rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
You are only working on one table so you sholdn't have
to manage a transaction or deal with explicit locking.
Just let the database handle this for you with a
sequence. Your concurrency issues will disappear.
1) create a sequence:
create sequence entry_no_sequence
2) set the new sequence's value to your table's
current entry_no value (n):
select setval('entry_no_sequence',n)
3) recreate your table so that the entry_no will get
it's value from calling nextval() on your new
sequence:
entry_no integer not null default
nextval('entry_no_sequence')
Thereafter, when an insert is made on your table, the
enry_no field will get its value from the sequence and
the sequence will be incremented. You would then drop
entro_no from your insert statement and it would
become something like:
INSERT INTO rcp_patient_visit_monitor (
patient_id, visit_date, is_newpatient, visit_type,
is_medical, is_review,
is_labtest, is_scan, is_scopy, is_xray, weight,
height)
VALUES (?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ash_grv7@yahoo.com
Subject: Re: concurrency problem
In-Reply-To: <20060616140855.72700.qmail@web52513.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox