pg_locks needs a facelift

From: Tom Lane
Subject: pg_locks needs a facelift
Date: ,
Msg-id: 18915.1114895559@sss.pgh.pa.us
(view: Whole thread, Raw)
List: pgsql-hackers

Tree view

pg_locks needs a facelift  (Tom Lane, )
 Re: pg_locks needs a facelift  ("Merlin Moncure", )
  Re: pg_locks needs a facelift  (Tom Lane, )
 Re: pg_locks needs a facelift  ("Merlin Moncure", )
  Re: pg_locks needs a facelift  (Alvaro Herrera, )
  Re: pg_locks needs a facelift  (Tom Lane, )
   Re: pg_locks needs a facelift  ("Jim C. Nasby", )
 Re: pg_locks needs a facelift  ("Merlin Moncure", )
 Re: pg_locks needs a facelift  ("Merlin Moncure", )
  Re: pg_locks needs a facelift  (Tom Lane, )
  Re: pg_locks needs a facelift  ("Jim C. Nasby", )
 Re: pg_locks needs a facelift  ("Merlin Moncure", )
  Re: pg_locks needs a facelift  (Alvaro Herrera, )
  Re: pg_locks needs a facelift  (Tom Lane, )
 Re: pg_locks needs a facelift  ("Merlin Moncure", )
  Re: pg_locks needs a facelift  ("Jim C. Nasby", )
   Re: pg_locks needs a facelift  (Tom Lane, )
    Re: pg_locks needs a facelift  ("Jim C. Nasby", )
 Re: pg_locks needs a facelift  ("Merlin Moncure", )

There was some previous discussion in a thread starting at
http://archives.postgresql.org/pgsql-hackers/2005-01/msg00750.php
about expanding the pg_locks view to provide more useful support
for contrib/userlock locks.  Also, the changes Alvaro and I just
finished for sharable row locks mean that the core system itself
is using lock types that aren't adequately displayed by pg_locks.
So we need to do something.

In the earlier thread there was talk of separate views for system
and user locks, but on reflection I think that's the wrong approach;
principally because it will be impossible to get exactly-simultaneous
snapshots of the system and user lock states if there are two views
involved.  And that's something you tend to want when studying lock
behavior ;-).  So I think we have to maintain the current arrangement
of one view, and add enough columns to it to handle all the
requirements.

As things are set up in CVS tip, LOCKTAG is defined to support these
kinds of locks:
   LOCKTAG_RELATION,           /* whole relation */   /* ID info for a relation is DB OID + REL OID; DB OID = 0 if
shared*/   LOCKTAG_RELATION_EXTEND,    /* the right to extend a relation */   /* same ID info as RELATION */
LOCKTAG_PAGE,              /* one page of a relation */   /* ID info for a page is RELATION info + BlockNumber */
LOCKTAG_TUPLE,             /* one physical tuple */   /* ID info for a tuple is PAGE info + OffsetNumber */
LOCKTAG_TRANSACTION,       /* transaction (for waiting for xact done) */   /* ID info for a transaction is its
TransactionId*/   LOCKTAG_OBJECT,             /* non-relation database object */   /* ID info for an object is DB OID +
CLASSOID + OBJECT OID + SUBID */   /*    * Note: object ID has same representation as in pg_depend and    *
pg_description,but notice that we are constraining SUBID to 16 bits.    * Also, we use DB OID = 0 for shared objects
suchas tablespaces.    */   LOCKTAG_USERLOCK            /* reserved for contrib/userlock */   /* ID info for a userlock
isdefined by user_locks.c */
 

and the physical struct provides four ID fields that are mapped in
various ways for the given purposes:

typedef struct LOCKTAG
{   uint32        locktag_field1;          /* a 32-bit ID field */   uint32        locktag_field2;          /* a 32-bit
IDfield */   uint32        locktag_field3;          /* a 32-bit ID field */   uint16        locktag_field4;          /*
a16-bit ID field */   uint8         locktag_type;            /* see enum LockTagType */   uint8
locktag_lockmethodid;   /* lockmethod indicator */
 
} LOCKTAG;

One way we could go is to just expose the four ID fields more or less
as-is, plus the type field, and let the user worry about interpreting
them.  I'm not very excited about that though, mainly because it would
be very non-backwards-compatible.  I would prefer to maintain the
existing columns of pg_locks with the existing definitions as much as
possible, and solve our problem by adding columns.

So what I'm thinking about is pg_locks having the following columns:

locktype    text
one of "relation", "relation_extend", "page", "tuple","transaction", "object", "user", or "unknown"

database    oid        *
the database OID (possibly zero) when relevant to the locktype,else null

relation    oid        *
the relation OID when relevant to the locktype,    else null

page        int4
the block number when relevant (PAGE and TUPLE locks), else null

offset        int2
the tuple offset when relevant (TUPLE locks only), else null

transaction    xid        *
the transaction ID when relevant (only for TRANSACTION locks),else null

classid        oid
the class OID when relevant (only for OBJECT locks), else null

objid        oid
the object OID when relevant (only for OBJECT locks), else null

objsubid    int2
the object sub-id when relevant (only for OBJECT locks), else null

pid        int4        *
mode        text        *
granted        bool        *
same as in current definition

The columns marked * are in the current definition, the others are new.

This still leaves us with the issue of what to do with user locks.
I am inclined to display them as if they were OBJECT locks, ie, fill
the database, classid, objid, and objsubid columns.  An alternative
that would also expose all the info is to display them as if they
were TUPLE locks.  Or we could add still more columns, but I'm not
real enthused about that idea.

Note that I'm not thinking of exposing lockmethodid --- that's redundant
with locktype in the current system, and this isn't likely to change.

Thoughts anyone?
        regards, tom lane



pgsql-hackers by date:

From: Mischa Sandberg
Date:
Subject: OLAP and PG and deja-vu
From: Robert Treat
Date:
Subject: Re: [pgsql-advocacy] Increased company involvement