regarding threads and transactions - problem 2

["Surabhi Ahuja " <surabhi.ahuja@xxxxxxxxxxx>]

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

Attachment: testRemoteConnPG.cxx
Description: testRemoteConnPG.cxx

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend