I wrote:
> BTW, I think your query is probably missing some cases:
> ( blockingl.transactionid=blockedl.transactionid
> OR
> (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
> )
> This supposes that locks of different strengths don't block each other,
> which is certainly wrong.
Oh, wait, scratch that: I was confusing locktype with mode. I'm still
suspicious about whether this is covering all cases though. It's
certainly not trying very hard to make sure the locks match up; though
that would tend to lead to too many reports not too few. Still, you could
be missing a lock conflict on some other kind of lock. I'd suggest
writing this part along the lines of
(blockingl.locktype,
blockingl.database,
blockingl.relation,
blockingl.page,
blockingl.tuple,
blockingl.virtualxid,
blockingl.transactionid,
blockingl.classid,
blockingl.objid,
blockingl.objsubid)
IS NOT DISTINCT FROM
(blockedl.locktype,
blockedl.database,
blockedl.relation,
blockedl.page,
blockedl.tuple,
blockedl.virtualxid,
blockedl.transactionid,
blockedl.classid,
blockedl.objid,
blockedl.objsubid)
which should reliably give the right answer.
regards, tom lane