Re: troubleshooting deadlocks

"Ed L." <pgsql@bluepolka.net> writes:
> I know the original statement is printed right after this, but with complex
> triggers doing lots of write queries, I'm finding it difficult to identify
> which subsequent query in the trigger is really the one immediately
> preceding the deadlock.  It would be helpful in debugging if the error
> message included info on which tables are involved, maybe even the
> deadlocking query itself, in the "DETAIL" output for future releases.

I suppose the problem here has to do with conflicting SELECT FOR UPDATEs
from foreign-key references.  This does actually work nicely in CVS tip,
for non-deferred FKs:

regression=# select boo(2);
ERROR:  insert or update on table "zork" violates foreign key constraint "zork_f1_fkey"
DETAIL:  Key (f1)=(2) is not present in table "zork2".
CONTEXT:  SQL statement "insert into zork values( $1 )"
PL/pgSQL function "boo" line 2 at SQL statement
regression=#

For deferred FKs you're pretty much out of luck, because the original
statement is long gone by the time the FK trigger fires (and no, it's
not reasonable to keep a copy around).  The difficulty in making 7.4
do this is that in 7.4 AFTER triggers never fire within a user function,
so you're always dealing with the "deferred" situation.

            regards, tom lane