Horton

Thread

  1. Small patch to replace 'idle' by 'trans' if transaction is still open

    Christof Petig <christof.petig@wtal.de> — 2000-10-03T19:28:36Z

    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

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-10-10T00:16:16Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-10T02:46:56Z

    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

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

    Christof Petig <christof.petig@wtal.de> — 2000-10-11T06:54:29Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-11T14:03:00Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-11T14:54:17Z

    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

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-10-11T15:14:56Z

    > 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

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

    Philip Warner <pjw@rhyme.com.au> — 2000-10-11T15:14:58Z

    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   |/

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-11T15:15:54Z

    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

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

    Philip Warner <pjw@rhyme.com.au> — 2000-10-11T16:12:47Z

    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   |/

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

    Peter Eisentraut <peter_e@gmx.net> — 2000-10-11T16:47:47Z

    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/

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-10-13T01:53:15Z

    > 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

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

    Philip Warner <pjw@rhyme.com.au> — 2000-10-13T01:59:09Z

    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   |/

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-14T00:51:27Z

    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

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

    Philip Warner <pjw@rhyme.com.au> — 2000-10-14T12:27:41Z

    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   |/

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-14T16:27:50Z

    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

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-19T17:25:18Z

    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

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

    Marc G. Fournier <scrappy@hub.org> — 2001-01-19T18:21:00Z

    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

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-19T20:59:41Z

    > 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

  20. Re: [PATCHES] Small patch to replace 'idle' by 'trans' if transactionis still open

    Christof Petig <christof@petig-baender.de> — 2001-01-22T07:51:33Z

    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.

  21. Re: [PATCHES] Small patch to replace 'idle' by 'trans' if transactionis still open

    Marc G. Fournier <scrappy@hub.org> — 2001-01-22T13:00:49Z

    Well, I'm still for this ... how about something simiple like 'idle in
trans'?  show, and easy to figure out what it means?

On Mon, 22 Jan 2001, Christof Petig wrote:

> 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.
>
>
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

  22. Re: [PATCHES] Small patch to replace 'idle' by 'trans' if transactionis still open

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-24T03:46:51Z

    Any solid consensus on this?

> 
> Well, I'm still for this ... how about something simiple like 'idle in
> trans'?  show, and easy to figure out what it means?
> 
> On Mon, 22 Jan 2001, Christof Petig wrote:
> 
> > 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.
> >
> >
> >
> 
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org
> 
> 


-- 
  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

  23. Re: [PATCHES] Small patch to replace 'idle' by 'trans' if transactionis still open

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-24T04:41:10Z

    Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Any solid consensus on this?

I'm for it (given a short status string --- "idle in tx" or "idle in trans"
seem fine).  Marc's for it.  Who's against it?

			regards, tom lane

  24. Re: [PATCHES] Small patch to replace 'idle' by 'trans' if transactionis still open

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-24T05:07:39Z

    I liked the 'idle in trans' because people can search for just the first
word if they want.

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Any solid consensus on this?
> 
> I'm for it (given a short status string --- "idle in tx" or "idle in trans"
> seem fine).  Marc's for it.  Who's against it?
> 
> 			regards, tom lane
> 


-- 
  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

  25. Re: [PATCHES] Small patch to replace 'idle' by 'trans' if transactionis still open

    Peter Eisentraut <peter_e@gmx.net> — 2001-01-24T15:53:05Z

    Tom Lane writes:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Any solid consensus on this?
>
> I'm for it (given a short status string --- "idle in tx" or "idle in trans"
> seem fine).  Marc's for it.  Who's against it?

(I'm not.)  If you're going to do "idle in trans" you might as well spell
it out.  The string will be truncated to the allowed length with no penalty.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

  26. Re: [PATCHES] Small patch to replace 'idle' by 'trans' if transactionis still open

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-24T15:53:43Z

    > Tom Lane writes:
> 
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Any solid consensus on this?
> >
> > I'm for it (given a short status string --- "idle in tx" or "idle in trans"
> > seem fine).  Marc's for it.  Who's against it?
> 
> (I'm not.)  If you're going to do "idle in trans" you might as well spell
> it out.  The string will be truncated to the allowed length with no penalty.

OK, patch applied:
	
	#$ ps -ax|grep post
	27085  ??  Ss     0:00.05 ./bin/postmaster -B 384 -i -d0 -o -F -d0
	27108  ??  S      0:00.04 postgres test [local] idle (postmaster)
	27150  p7  S+     0:00.01 grep post
	#$ ps -ax|grep post
	27085  ??  Is     0:00.05 ./bin/postmaster -B 384 -i -d0 -o -F -d0
	27108  ??  S      0:00.04 postgres test [local] idle in transaction (postmaster
	27185  p7  S+     0:00.02 grep post
	#$ 

-- 
  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