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.
Dave
On 18-Apr-07, at 11:36 AM, Csaba Nagy wrote:
Can someone confirm that I've identified the right fix?
I'm pretty sure that won't help you... see:
http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php
The deadlock will be there if you update/insert the child table and
update/insert the parent table in the same transaction (even if you
update some other field on the parent table than the key referenced by
the child table). If your transactions always update/insert only
one of
those tables, it won't deadlock (assuming you order the inserts/
updates
properly per PK).
Cheers,
Csaba.
---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
