From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Ed L(dot)" <pgsql(at)bluepolka(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: troubleshooting deadlocks |
Date: | 2004-11-09 17:36:30 |
Message-ID: | 21590.1100021790@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Ed L." <pgsql(at)bluepolka(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Gary L. Burnore | 2004-11-09 18:00:00 | Re: comp.os.linux.answers - MVI RESULT |
Previous Message | Tk421 | 2004-11-09 17:33:00 | Re: Functions in postgres |