Re: RFC: replace pg_stat_activity.waiting with something more descriptive

On Mon, Jun 22, 2015 at 4:09 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Mon, Jun 22, 2015 at 1:59 PM, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > In addition to the codes themselves I think it would aid less-experienced
> > operators if we would provide a meta-data categorization of the codes.
> > Something like, I/O Sub-System; Storage Maintenance; Concurrency, etc..
> >
> > There could be a section in the documentation with these topics as
> section
> > headings and a listing and explanation of each of the possible code would
> > then be described within.
> >
> > The meta-information is already embedded within the code/descriptions but
> > explicitly pulling them out would be, IMO, more user-friendly and likely
> > also aid in triage and speed-of-recognition when reading the
> corresponding
> > code/description.
>
> I was thinking that the codes would probably be fairly straightforward
> renderings of the underlying C identifiers, e.g.:
>
> Lock (Relation)
> Lock (Relation Extension)
> Lock (Page)
> ...
> ...
> LWLock (ShmemIndexLock)
> LWLock (OidGenLock)
> LWLock (XidGenLock)
> LWLock (ProcArrayLock)
> ...
> ...
> Spin Lock Delay
> Buffer Cleanup Lock
>
> We'd then have to figure out how to document that stuff.
>
>
​Just tossing stuff at the wall...​

​CREATE TABLE pg_​stat_wait_event_info (
​​wait_event integer PRIMARY KEY,
category text, --possibly a FK to a description-holding table too
event_type text, --Lock, LWLock, etc...or something higher-level; was
pondering whether ltree could be brought into core and used here...
event_documentation --asciidoc or something similar
);

​Add \psql commands:

\​dproc [ proc_id] --runs pg_stat_activity more-or-less
\dproc+ proc_id --shows the event information w/ description; and ideally
info from pg_locks among other possibilities

That said, the same documentation should be made available online as well -
but having this allows tools to make use the info to put the documentation
closer to the user.

David J.