DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=kP0vWpAz6eXt2jgnUYN3e/0UnqT7ZBY+mCHsBxkFYS1vj6OZQ6VZDUIZkk5jAD/QN5L7SaxM/iLMoAfdIaDuOTgaKCCZ6vH/tzsIvELhPbQVPefr9T4asQNTkp151YSHIqwALnh7lCtmMiu3OO36A5JgOF43fRkDE/935+SD85k=
	;
Message-ID: <20060616140855.72700.qmail@web52513.mail.yahoo.com>
Date: Fri, 16 Jun 2006 07:08:55 -0700 (PDT)
From: Ash Grove <ash_grv7@yahoo.com>
Subject: Re: concurrency problem
To: pgsql-sql@postgresql.org
In-Reply-To: <ee0ad3a30606152114g122d0021ja7c603a8f36ae62c@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


>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