Dave Cramer escribió: > Hi Csaba, > > I have a similar problem. > > In an attempt to avoid the overhead of select count(*) from mailbox > where uid = somuid I've implemented triggers on insert and delete. > > So there is a > > user table which refers to to an inbox table, > > so when people insert into the inbox there is an RI trigger grabbing > the shared lock, then the count triggers try to grab an exclusive > lock resulting in a deadlock. > > Can we safely remove the shared locks ? > > Is there a right way to implement the count triggers. I've tried > before triggers, and after triggers, both result in different kinds > of deadlocks. Would it be possible for the triggers to lock the records, before starting the actual operation, in well known orders, to avoid the deadlocks? A frequently mentioned approach to avoid the point of contention is to have a "totals" record and have the triggers insert "deltas" records; to get the sum, add them all. Periodically, take the deltas and apply them to the totals. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
