threads and transaction ...sample code and stored procedure

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

 Hello all,

this is in regards to a problem that i am facing ..in case of executing transaction in a multi threaded environment.

please take some time out to study it and help me.

The programs is as follows:

(see attachment)

the output on running it as follows:( i have marked the output ..in red ..at the places where i dont expect to see it...)

 

Connection Made

Status is : PGRES_COMMAND_OK

Result message :

Connection Made

Status is : PGRES_COMMAND_OK

Result message :

Connection Made

Co

Status is : PGRES_COMMAND_OK

Result message : nnection Made

Status is : PGRES_TUPLES_OK

Result message :

number of rows = 1 , fields returned = 1

(721,t)

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_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_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_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 : All threads completed successfully

 

in the code ..the stored procedure 'insert_patient' is being called it is a s 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;

LOCK TABLE patient in ACCESS EXCLUSIVE mode;

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;

 

Ok the expected output is:

now instead of all the red sentences ..there are 4 of such red blocks ..corresponding to 4 out of 5 threads

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

 

however i want to see ..

Status is : PGRES_TUPLES_OK

Result message :

number of rows = 1 , fields returned = 1

(721,f)

at each of those 4 places ....

please take some time out ..for this...

Regards

Surabhi Ahuja

Attachment: testRemoteConnPG.cxx
Description: testRemoteConnPG.cxx

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly