> The use case is something like:-
>> ·There is a table let’s say “A” and the trigger is created on this table
>> let say “A_TRIGGER”.
>> ·The trigger captures the data change happens in table A into table B.
>> ·There would be a huge insert, update, delete on table A, the side
>> effect of it table B also get updated very frequently.
In my recent observation I seen deadlocks with ShareLock, when any trigger
doing with INSERT/UPDATE/DELETE.
As Robins Tharakan said its also with any blocking operation like REINDEX.
If your logs looking like
2011-07-09 04:10:44 ETC/GMT ERROR: deadlock detected
2011-07-09 04:10:44 ETC/GMT DETAIL: Process 22986 waits for ShareLock on
transaction 939; blocked by process 22959.
Then I say, you need to give a try by testing Lock on Parent Table (i.e. in
your case A) by *SHARE UPDATE EXCLUSIVE MODE *in trigger calling function.
CREATE OR REPLACE FUNCTION CHILD_TEST() RETURNS TRIGGER
* LOCK TABLE PARENT IN SHARE UPDATE EXCLUSIVE MODE;*
UPDATE PARENT SET A=NEW.A;
Here *SHARE UPDATE EXCLUSIVE MODE *Works as a Select *For update* clause.
I believe you should give a try test on this.
In response to
pgsql-admin by date
|Next:||From: Ramiro Barreca||Date: 2011-11-11 15:05:43|
|Subject: Full text search: How to implement?|
|Previous:||From: Karuna Karpe||Date: 2011-11-11 05:10:37|
|Subject: Re: error log, tablespace, wal files|