Re: reporting reason for certain locks

On Mon, Nov 22, 2010 at 5:55 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> Hi,
>
> When we lock on a Xid or VirtualXid, there's no way to obtain clear
> information on the reason for locking.  Consider the following example:
>
> CREATE TABLE foo (a int);
>
> Session 1:
> BEGIN;
> SELECT 1;
> -- we now have a snapshot
>
> Session 2:
> CREATE INDEX CONCURRENTLY foo_a ON foo(a);
>
> This blocks until transaction 1 commits, and it's not obvious to the
> user the reason for this.  There's some info in pg_locks but it just
> says it's blocked in a VirtualXid.
>
> 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.
>
> A simple idea I had was that each backend would have a reserved shared
> memory area where they would write what they are about to lock, when
> locking an Xid or VXid.  Thus, if they block, someone else can examine
> that and make the situation clearer.  The problem with this idea is that
> it would require locking a LWLock just before trying each lock on
> Xid/VXid, which would be horrible for performance.
>
> ... or maybe not, because when we call XactLockTableWait, we've already
> established that we've accepted to sleep.
>
> Thoughts?

How about publishing additional details to pg_stat_activity via
pgstat_report_waiting()?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company