Dear All,
This is in reference to a problem which I had put up sometime back.
Please take some time to study it
The piece of code that i am trying to execute is attached (itsa cpp file)
The stored procedure (that the program calls) insert_patient is as follows:
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;
The output that i am getting (on executing it on a dual processor machine) is as follows:
Connection Made
Connection Made
Status is : PGRES_COMMAND_OK
Result message :
Status is : PGRES_COMMAND_OK
Result message :
Status is : PGRES_TUPLES_OK
Result message :
number of rows = 1 , fields returned = 1
(2,t)
Connection Made
Connection Made
Status is : PGRES_COMMAND_OK
Result message :
Status is : PGRES_COMMAND_OK
Result message :
********Status is : PGRES_COMMAND_OK
********Result message :
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
)"
PL/pgSQL function "insert_patient" line 11 at SQL statement
********Status is : PGRES_COMMAND_OK
********Result message :
Status is : PGRES_TUPLES_OK
Result message :
number of rows = 1 , fields returned = 1
(2,f)
Status is : PGRES_TUPLES_OK
Result message :
********Status is : PGRES_COMMAND_OK
********Result message :
number of rows = 1 , fields returned = 1
(2,f)
********Status is : PGRES_COMMAND_OK
********Result message :
Connection Made
Status is : PGRES_COMMAND_OK
Result message :
Status is : PGRES_TUPLES_OK
Result message :
number of rows = 1 , fields returned = 1
(2,f)
********Status is : PGRES_COMMAND_OK
********Result message : All threads completed successfully
Observations:
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?
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
Please Note: I am using PostgreSQL 8.0.0
and the transaction level is read_committed.
Thank You
Regards
Surabhi Ahuja