regarding threads and transactions - problem 2 - Mailing list pgsql-general

 

 

 

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

 

 

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

 

 and the transaction level is read_committed.

 

Thank You

Regards

Surabhi Ahuja