Thread: Small patch to replace 'idle' by 'trans' if transaction is still open
If you are looking for programs which tend to hold longstanding transactions, this micro patch might be handy. Whether it is of general use is debatable. It will replace 'idle' by 'trans' if the backend is idle but a transaction is pending. Simply use ps to view the backend's status. Christof
Attachment
I can't decide if this is of general use. My inclination is that someone in a transaction sitting a prompt should still show as idle. > If you are looking for programs which tend to hold longstanding > transactions, this micro patch might be handy. > Whether it is of general use is debatable. It will replace 'idle' by > 'trans' if the backend is idle but a transaction is pending. > > Simply use ps to view the backend's status. > > Christof [ application/x-gzip is not supported, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can't decide if this is of general use. My inclination is that
> someone in a transaction sitting a prompt should still show as idle.
The idea seemed good to me, although I didn't look at the code to see
if the implementation was any good ;-). I know we've frequently had
questions on the lists where it was interesting to know if any
transactions were being held open --- and right now there's no easy
way to tell.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I can't decide if this is of general use. My inclination is that > > someone in a transaction sitting a prompt should still show as idle. > > The idea seemed good to me, although I didn't look at the code to see > if the implementation was any good ;-). I know we've frequently had > questions on the lists where it was interesting to know if any > transactions were being held open --- and right now there's no easy > way to tell. > > regards, tom lane I would even propose 'idle (transaction open)' if you agree. (Seemed too long for me) The patch is _trivial_ a string changed to a condifional operator and two strings (two times). A two liner. Christof
Christof Petig <christof.petig@wtal.de> writes:
> I would even propose 'idle (transaction open)' if you agree. (Seemed too
> long for me)
No, the ps strings have got to be *short*. A lot of platforms have a
tight limit on how much command string ps will show. I thought 'trans'
was OK, though perhaps someone has an even better idea?
regards, tom lane
At 10:03 11/10/00 -0400, Tom Lane wrote:
>Christof Petig <christof.petig@wtal.de> writes:
>> I would even propose 'idle (transaction open)' if you agree. (Seemed too
>> long for me)
>
>No, the ps strings have got to be *short*. A lot of platforms have a
>tight limit on how much command string ps will show. I thought 'trans'
>was OK, though perhaps someone has an even better idea?
Any chance of showing if anything has been updated in the TX? Then the text
could be:
RW TX
or
RO TX
If not, then how about 'TX Active' or 'TX Open' or just 'In TX'
----------------------------------------------------------------
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 |/
Philip Warner <pjw@rhyme.com.au> writes:
> Any chance of showing if anything has been updated in the TX?
Hmm ... SharedBufferChanged would probably do as a proxy for that ...
not sure if it's a good idea though.
regards, tom lane
At 10:54 11/10/00 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> Any chance of showing if anything has been updated in the TX?
>
>Hmm ... SharedBufferChanged would probably do as a proxy for that ...
>not sure if it's a good idea though.
The idea was to indicate if the process is likely to be causing a locking
problem - but now I think about it, a more useful pice of information would
be if any tables/rows/whatever are locked in such a way that another user
might be prevented from doing an update. Is this possible?
I would maintain that if possible, it is useful to distinguish logical
'read-only' transactions from TXs that might lock another user.
----------------------------------------------------------------
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 |/
> Christof Petig <christof.petig@wtal.de> writes: > > I would even propose 'idle (transaction open)' if you agree. (Seemed too > > long for me) > > No, the ps strings have got to be *short*. A lot of platforms have a > tight limit on how much command string ps will show. I thought 'trans' > was OK, though perhaps someone has an even better idea? idle/trans? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Philip Warner <pjw@rhyme.com.au> writes:
> The idea was to indicate if the process is likely to be causing a locking
> problem - but now I think about it, a more useful pice of information would
> be if any tables/rows/whatever are locked in such a way that another user
> might be prevented from doing an update. Is this possible?
Don't see how you could convey a useful amount of info in the space
available. There are way too many levels of way too many locks that
might or might not conflict with someone else. (Even a read-only
transaction grabs read locks, so it's not like the presence or absence
of any lock is sufficient info to be useful.)
There was another thread recently about adding a SHOW command or some
such to dump out the state of the lock manager's table in a readable
form. I think that'd be a more useful thing to work on than trying
to cram one or two bits' worth of info into the ps display.
regards, tom lane
Re: Small patch to replace 'idle' by 'trans' if transaction is still open
From
Peter Eisentraut
Date:
Tom Lane writes: > Christof Petig <christof.petig@wtal.de> writes: > > I would even propose 'idle (transaction open)' if you agree. (Seemed too > > long for me) > > No, the ps strings have got to be *short*. A lot of platforms have a > tight limit on how much command string ps will show. I thought 'trans' > was OK, though perhaps someone has an even better idea? Since nothing follows the status indicator in the ps display you can really make it as long as you want; the rest will be truncated. So you might want to put it "transaction (idle)", so that the essential information is first. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
At 11:15 11/10/00 -0400, Tom Lane wrote:
>
>There was another thread recently about adding a SHOW command or some
>such to dump out the state of the lock manager's table in a readable
>form. I think that'd be a more useful thing to work on than trying
>to cram one or two bits' worth of info into the ps display.
>
Absolutely. I assume from this that the nature and state of all locks are
stored in shared memory somewhere, and that the data is able to be
interpreted without reference to non-shared data. If so, this would seem to
be (a) a very useful thing to have and (b) not too hard. Is that right?
----------------------------------------------------------------
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 |/
> At 11:15 11/10/00 -0400, Tom Lane wrote:
> >
> >There was another thread recently about adding a SHOW command or some
> >such to dump out the state of the lock manager's table in a readable
> >form. I think that'd be a more useful thing to work on than trying
> >to cram one or two bits' worth of info into the ps display.
> >
>
> Absolutely. I assume from this that the nature and state of all locks are
> stored in shared memory somewhere, and that the data is able to be
> interpreted without reference to non-shared data. If so, this would seem to
> be (a) a very useful thing to have and (b) not too hard. Is that right?
>
Added to TODO:
* Add SHOW command to display locks
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Philip Warner <pjw@rhyme.com.au> writes:
>> There was another thread recently about adding a SHOW command or some
>> such to dump out the state of the lock manager's table in a readable
>> form. I think that'd be a more useful thing to work on than trying
>> to cram one or two bits' worth of info into the ps display.
> Absolutely. I assume from this that the nature and state of all locks are
> stored in shared memory somewhere, and that the data is able to be
> interpreted without reference to non-shared data. If so, this would seem to
> be (a) a very useful thing to have and (b) not too hard. Is that right?
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.)
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...
regards, tom lane
At 20:51 13/10/00 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.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 |/
Philip Warner <pjw@rhyme.com.au> writes:
> Sounds like a good start. But would it be possible/desirable for a utility
> program to map the lock manager tables directly?
The utility program would have to be able to attach to the shared memory
segments holding the locktable and associated spinlocks. And if you
did it that way, you couldn't inspect the locktable remotely without
duplicating a bunch more postmaster/backend code. Offhand it seems to
me that "start an additional backend and use it to look at the
locktable" is the best way to approach this.
This does raise an interesting point though: do we need any security on
this new SHOW command? Like, say, only allow it to superusers?
regards, tom lane
Seems we decided against this. Sorry. > Tom Lane wrote: > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > I can't decide if this is of general use. My inclination is that > > > someone in a transaction sitting a prompt should still show as idle. > > > > The idea seemed good to me, although I didn't look at the code to see > > if the implementation was any good ;-). I know we've frequently had > > questions on the lists where it was interesting to know if any > > transactions were being held open --- and right now there's no easy > > way to tell. > > > > regards, tom lane > > I would even propose 'idle (transaction open)' if you agree. (Seemed too > long for me) > The patch is _trivial_ a string changed to a condifional operator and two > strings (two times). A two liner. > > Christof > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Re: Small patch to replace 'idle' by 'trans' if transaction is still open
From
The Hermit Hacker
Date:
On Fri, 19 Jan 2001, Bruce Momjian wrote: > > Seems we decided against this. Sorry. Huh? from reading Tom's response, sounds like it would be something useful? I know I find having as much information about state in the ps listing helps me alot, and knowing if its 'idle' vs 'idle (in transaction)' provides at lesat more detailed information then just 'idle' ... Who was the 'we' in the above decision? Tom seemed in favor of it, I know I'm in favor of it .. and you are not in favor of it ... > > > Tom Lane wrote: > > > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > > I can't decide if this is of general use. My inclination is that > > > > someone in a transaction sitting a prompt should still show as idle. > > > > > > The idea seemed good to me, although I didn't look at the code to see > > > if the implementation was any good ;-). I know we've frequently had > > > questions on the lists where it was interesting to know if any > > > transactions were being held open --- and right now there's no easy > > > way to tell. > > > > > > regards, tom lane > > > > I would even propose 'idle (transaction open)' if you agree. (Seemed too > > long for me) > > The patch is _trivial_ a string changed to a condifional operator and two > > strings (two times). A two liner. > > > > Christof > > > > > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> On Fri, 19 Jan 2001, Bruce Momjian wrote: > > > > > Seems we decided against this. Sorry. > > Huh? from reading Tom's response, sounds like it would be something > useful? I know I find having as much information about state in the ps > listing helps me alot, and knowing if its 'idle' vs 'idle (in > transaction)' provides at lesat more detailed information then just 'idle' > Who was the 'we' in the above decision? Tom seemed in favor of it, I know > I'm in favor of it .. and you are not in favor of it ... There must have been some discussion about it. I don't see it in the code, and I remember it was rejected for some reason. Check the archives. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
If you don't know what is all about read the bottom (I'll requote my posting)
Bruce Momjian wrote:
> > On Fri, 19 Jan 2001, Bruce Momjian wrote:
> > > Seems we decided against this. Sorry.
> >
> > Huh? from reading Tom's response, sounds like it would be something
> > useful? I know I find having as much information about state in the ps
> > listing helps me alot, and knowing if its 'idle' vs 'idle (in
> > transaction)' provides at lesat more detailed information then just 'idle'
>
> > Who was the 'we' in the above decision? Tom seemed in favor of it, I know
> > I'm in favor of it .. and you are not in favor of it ...
>
> There must have been some discussion about it. I don't see it in the
> code, and I remember it was rejected for some reason. Check the archives.
The thing which comes most close to a rejection was the 'I can't decide' mail
by you (answered by Tom). The conclusion sounded like 'since we're not clear on
this subject we won't touch this, yet'. And there was some unsettled discussion
about the best wording to show in 'ps'.
'trans' seemed too short (and too much unlike 'idle') (as indicated by Bruce)
and 'idle (open transaction)' might give difficulties on platforms which limit
the length of the string (as indicated by Tom)
I'll CC Hackers (where this discussion belongs)
Christof
---------------------
Quoting:
Subject: Re: [PATCHES] Small patch to replace 'idle' by 'trans' if transaction
is still open
Date: Mon, 09 Oct 2000 22:46:56 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can't decide if this is of general use. My inclination is that
> someone in a transaction sitting a prompt should still show as idle.
The idea seemed good to me, although I didn't look at the code to see
if the implementation was any good ;-). I know we've frequently had
questions on the lists where it was interesting to know if any
transactions were being held open --- and right now there's no easy
way to tell.
regards, tom lane
--------------
Subject: Small patch to replace 'idle' by 'trans' if transaction is still open
Date: Tue, 03 Oct 2000 21:28:36 +0200
From: Christof Petig <christof.petig@wtal.de>
If you are looking for programs which tend to hold longstanding
transactions, this micro patch might be handy.
Whether it is of general use is debatable. It will replace 'idle' by
'trans' if the backend is idle but a transaction is pending.
Simply use ps to view the backend's status.
Christof
--- src/backend/commands/async.c~ Sun May 14 05:18:35 2000
+++ src/backend/commands/async.c Tue Oct 3 10:31:54 2000
@@ -818,7 +818,7 @@
*/
pq_flush();
- PS_SET_STATUS("idle");
+ PS_SET_STATUS(IsTransactionBlock()?"trans":"idle");
TPRINTF(TRACE_NOTIFY, "ProcessIncomingNotify: done");
}
--- src/backend/tcop/postgres.c~ Thu Aug 31 09:18:57 2000
+++ src/backend/tcop/postgres.c Tue Oct 3 10:32:23 2000
@@ -1496,7 +1496,7 @@
for (;;)
{
- PS_SET_STATUS("idle");
+ PS_SET_STATUS(IsTransactionBlock()?"trans":"idle");
/* ----------------
* (1) tell the frontend we're ready for a new query.