Search Postgresql Archives

regarding threads and transactions - problem 2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux