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

"Surabhi Ahuja " <surabhi(dot)ahuja(at)iiitb(dot)ac(dot)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