Re: reporting reason for certain locks

Excerpts from Tom Lane's message of lun nov 22 20:51:09 -0300 2010:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> > A much more common ocurrence is tuple locks. We block in an Xid in that
> > case; and this has been a frequent question in the mailing lists and
> > IRC.
>
> > I think it would be very nice to be able to report something to the
> > user; however, I'm not seeing the mechanism.
>
> At least for tuple locks, the information is already visible, because we
> have a "real" lock on the target tuple before we try to lock the current
> holder's VXID. So I think this isn't so much a question of needing more
> low-level mechanism as one of providing a more useful view --- some kind
> of self-join on pg_locks is needed.

Hmm, that's true, but it seems ugly: if we are blocking on a
transactionid, then go back to pg_locks and extract a lock of type
"tuple"; if it's there, you know you're waiting for that; if it's not,
you have to guess that you're waiting on something else (what?).
(Right now, it seems the only other thing that could wait is CREATE
INDEX CONCURRENTLY, but I don't want to bet that we're not going to
create something else in the future. There's no way to figure out
what's happening from pg_locks, in any case.)

So what I want is something a bit more trustworthy than that.

On the other hand, pg_locks is already rather unwieldy to use. We
already have a self-join that tells us the details of what's locking
processes: you need to join pg_locks like this:

FROM
pg_catalog.pg_locks l1
JOIN
pg_catalog.pg_locks l2 ON (
(
l1.locktype, l1.database, l1.relation, l1.page,
l1.tuple, l1.virtualxid, l1.transactionid, l1.classid,
l1.objid, l1.objsubid
)
IS NOT DISTINCT FROM
(
l2.locktype, l2.database, l2.relation, l2.page,
l2.tuple, l2.virtualxid, l2.transactionid, l2.classid,
l2.objid, l2.objsubid
)
)

and throw in a bunch of left joins to see the details of database,
relation, etc. This works fine for all kinds of locks except xid and
vxid ones. I don't think it's fair to users to expect that they need to
deal with that mess *plus* the details of tuple locks.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support