Re: Small patch to replace 'idle' by 'trans' if transaction is still open

At 20:51 13/10/00 -0400, Tom Lane wrote:
>Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>
>The information is theoretically available in shared memory, but I'm
>not sure how hard it is to transform into a conveniently readable form
>(like "process X holds locks A, B, and C and is waiting on lock D").
>I know that the few times I've tried to grovel around in the lock table
>with a debugger, it's been pretty excruciating to figure out what was
>going on --- there's just barely enough info there for the code to work,
>and no overhead to aid in interpretation. You might find that adding
>some additional fields to locktable entries would be a good idea.
>(Or maybe not; I can tell you that it's no fun to try to follow it with
>just gdb print statements, but a piece of code might not have trouble.)

This sounds good; I wrote a similar lock-tracer for Dec RDB before they
introduced a utility for this purpose. It's relatively easy, technically,
so long at the lock manager has all the info. What I'd probably want to do
is (a) dump current processes and their state as well as their lock request
queue, then (b) assuming a stalled lock is identified, allow the user/DBA
to request a trace of the specific lock ID. Hope the terminology makes
sense in terms of the PGSQL lock manager. It would help if the lock manager
had the date/time a request was made.

>There already is some code in lock.c to print out the contents of
>the table, but it's only conditionally compiled (ifdef LOCK_DEBUG),
>has no way to be called except through hand intervention with a debugger,
>and furthermore is set up to dump to the backend's stdout, which is
>not especially convenient in most scenarios. A first cut would be
>to transform that code into a user-callable statement that reports
>via elog(NOTICE). Then we could look at the output and see whether
>it's useful for mere mortals or not...

Sounds like a good start. But would it be possible/desirable for a utility
program to map the lock manager tables directly?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/