Re: regarding threads and transactions - problem 2

patient_key is the unique key
and the primary key is patient_id, which is a bigserial.

actually this is what the stored procedure does:

a patient comes and it is associated with patient_key ...if is not present in the table, then insert it into the table.
when this patient gets inserted , the Stored procedure will return the id (bigserial) of this patient to the user.

and if the patient is alread in the table, then the user should get the id of this patient(which is alread present in the table).

....is not the stored procedure correctly coded according to the above scenario?


CREATE OR REPLACE FUNCTION insert_patient (varchar(65),varchar(65),date,varchar(256)) RETURNS retval AS'
DECLARE
patId bigint; oid1 int4;
val retval;
patKey text;
BEGIN
patKey := $4;
select patient_id into patId from patient where patient_key = patKey;
if not found
THEN
insert into patient(patient_name,org_pat_id,birth_date,patient_key) values($1,trim($2),$3,$4);
SELECT patient_id INTO val.id from patient where patient_key = patKey;
SELECT INTO val.insert TRUE;
else
val.id := patId;
SELECT INTO val.insert FALSE;
end if;
RETURN val;
END;
'LANGUAGE plpgsql;

________________________________

From: pgsql-general-owner(at)postgresql(dot)org on behalf of Richard Huxton
Sent: Fri 8/26/2005 2:02 PM
To: Surabhi Ahuja
Cc: pgsql-general(at)postgresql(dot)org; pingo(dot)bgm(at)gmail(dot)com
Subject: Re: [GENERAL] regarding threads and transactions - problem 2

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********

Surabhi Ahuja wrote:

> BEGIN
> patKey := $4;
> select patient_id into patId from patient where patient_key = patKey;
> if not found
> THEN
> insert into patient(patient_name,org_pat_id,birth_date,patient_key) values($1,trim($2),$3,$4);

> The output that i am getting (on executing it on a dual processor machine) is as follows:

> Status is : PGRES_FATAL_ERROR
> Result message : ERROR: duplicate key violates unique constraint "patient_patient_key_key"
> CONTEXT: SQL statement "insert into patient(patient_name,org_pat_id,birth_date,patient_key) values( $1 ,trim( $2 ), $3 , $4
> )"

> Please check the block in red. Why is it happening? insnt the call to the stored procedure considered one atomic operation?
> Please tell me what is going wrong?

(For those viewing in plain-text, the red block is the "duplicate pkey"
error)

> Cant I avoid such red blocks? and get messages like the ones obained from the other threads
> I can impose locks but would not that lower down the performance?
> Please suggest other solutions

There is no free solution to the problem of concurrent updates to the
same resource. You have two options:
1. Optimistically try the insert and if you get an error catch it and
issue the update instead.
2. Lock the resource for the duration of your update and deal with the
fact that some updates might time-out/fail to get the lock and need to
be retried.
3. Don't actually have a shared resource (e.g. use auto-generated
sequence values for meaningless ID numbers).

In a nutshell, those are the options available to you, but I would
recommend getting a good technical book on concurrency and spending a
couple of days with it.

In your example, I'm a little confused as to what your primary key is
(patient_id or patient_key) and what purpose the other column serves.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster