Thread: threads and transaction ...sample code and stored procedure

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

"Surabhi Ahuja " <surabhi.ahuja@iiitb.ac.in> writes:
> CREATE OR REPLACE FUNCTION insert_patient (varchar(65),varchar(65),date,var=
> char(256)) RETURNS retval AS'
> ...
> 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);

What Postgres version is this?  I'd only expect the above to work
properly in 8.0 and up.  Prior versions didn't advance the transaction
snapshot within plpgsql functions, so that even though you hold an
exclusive lock, the SELECT sees a snapshot of the table dating from
before the lock was taken, and thus possibly before a competing thread
put in the key you are looking for.  (Even in 8.0, it won't work in
SERIALIZABLE transaction mode.)

If you aren't in a position to update to 8.0, I think you have to issue
the commands from your application instead of wrapping them in a function.
Or at least do "BEGIN; LOCK TABLE; SELECT function(...); COMMIT;" from
the application.

BTW, ACCESS EXCLUSIVE lock is overkill and will probably just lead to
deadlocks.  There's no need to lock out readers of the table, so why
not use plain EXCLUSIVE?

            regards, tom lane