Postgresql function lock error
I have a trigger that updates a cache table. The function executed by the trigger makes two operation: deletes the old cache row and adds a new cache row based on the id with a total
cache table columns: id | total
Because of the high level of server activity I believe that two paralles calls on the function will generate the following situation:
Delete 1 Delete 2 Insert 1 Insert 2 ( this will crash because of the primary key )
Is there any way I can prevent this ? Shouldn't the transaction prevent this ( the implied transaction in a postgresql function )
you can use a advisory locks. http://www.postgresql.org/docs/9.0/interactive/functions-admin.html
BEGIN PERFORM pg_advisory_lock(old.id); DELETE FROM cache WHERE some_id = old.id; INSERT INTO cache SELECT ...; PERFORM pg_advisory_unlock(old.id); RETURN old.id; END; $$ ...