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 )

Answers


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;
$$ ...

Pavel


Need Your Help

Clear eval in Leksah

haskell leksah

I am not sure this is the right place to ask this question. If it isn't - my apologies.

Android Alert Dialog Text Align

android dialog alert

How To Align Right Order Total Values Like Below Images.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.