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
Tue, 03 Oct 2000 21:28:36 +0200
Christof Petig <christof.petig@wtal.de>
Small patch to replace 'idle' by 'trans' if transaction is still open
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

Mon, 9 Oct 2000 20:16:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Small patch to replace 'idle' by 'trans' if transaction
	is still open
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

Mon, 09 Oct 2000 22:46:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Small patch to replace 'idle' by 'trans' if transaction is still
	open
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



Wed, 11 Oct 2000 08:54:29 +0200
Christof Petig <christof.petig@wtal.de>
Re: Small patch to replace 'idle' by 'trans' if transaction is
	still open
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

Wed, 11 Oct 2000 10:03:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Small patch to replace 'idle' by 'trans' if transaction is still
	open
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

Wed, 11 Oct 2000 10:54:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Small patch to replace 'idle' by 'trans' if transaction is still
	open
> 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

Wed, 11 Oct 2000 11:14:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Small patch to replace 'idle' by 'trans' if transaction
	is still open
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   |/

Thu, 12 Oct 2000 01:14:58 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Small patch to replace 'idle' by 'trans' if
	transaction is still open
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

Wed, 11 Oct 2000 11:15:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Small patch to replace 'idle' by 'trans' if transaction is still
	open
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   |/

Thu, 12 Oct 2000 02:12:47 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Small patch to replace 'idle' by 'trans' if
	transaction is still open
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/


Wed, 11 Oct 2000 18:47:47 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Small patch to replace 'idle' by 'trans' if transaction
	is still open
> 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

Thu, 12 Oct 2000 21:53:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Small patch to replace 'idle' by 'trans' if  transaction
	is still open
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   |/

Fri, 13 Oct 2000 11:59:09 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Small patch to replace 'idle' by 'trans' if
	transaction is still open
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

Fri, 13 Oct 2000 20:51:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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@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   |/

Sat, 14 Oct 2000 22:27:41 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Small patch to replace 'idle' by 'trans' if
	transaction is still open
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

Sat, 14 Oct 2000 12:27:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Small patch to replace 'idle' by 'trans' if transaction is still
	open

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

Fri, 19 Jan 2001 12:25:18 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Small patch to replace 'idle' by 'trans' if transaction
	is  still open
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


Fri, 19 Jan 2001 14:21:00 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: Small patch to replace 'idle' by 'trans' if transaction
	is  still open
> 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

Fri, 19 Jan 2001 15:59:41 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Small patch to replace 'idle' by 'trans' if transaction
	is  still open
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.



Mon, 22 Jan 2001 08:51:33 +0100
Christof Petig <christof@petig-baender.de>
Re: [PATCHES] Small patch to replace 'idle' by 'trans' if
	transactionis still open

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


Mon, 22 Jan 2001 09:00:49 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [PATCHES] Small patch to replace 'idle' by 'trans' if
	transactionis still open

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

Tue, 23 Jan 2001 22:46:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Small patch to replace 'idle' by 'trans' if
	transactionis still open
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

Tue, 23 Jan 2001 23:41:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [PATCHES] Small patch to replace 'idle' by 'trans' if
	transactionis still open
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

Wed, 24 Jan 2001 00:07:39 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Small patch to replace 'idle' by 'trans' if
	transactionis still open
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/


Wed, 24 Jan 2001 16:53:05 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [PATCHES] Small patch to replace 'idle' by 'trans' if
	transactionis still open
> 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

? config.log
? config.cache
? config.status
? GNUmakefile
? src/Makefile.custom
? src/GNUmakefile
? src/Makefile.global
? src/log
? src/crtags
? src/backend/postgres
? src/backend/catalog/global.description
? src/backend/catalog/global.bki
? src/backend/catalog/template1.bki
? src/backend/catalog/template1.description
? src/backend/port/Makefile
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_restore
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_id/pg_id
? src/bin/pg_passwd/pg_passwd
? src/bin/pgaccess/pgaccess
? src/bin/pgtclsh/Makefile.tkdefs
? src/bin/pgtclsh/Makefile.tcldefs
? src/bin/pgtclsh/pgtclsh
? src/bin/pgtclsh/pgtksh
? src/bin/psql/psql
? src/bin/scripts/createlang
? src/include/config.h
? src/include/stamp-h
? src/interfaces/ecpg/lib/libecpg.so.3.2.0
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpgeasy/libpgeasy.so.2.1
? src/interfaces/libpgtcl/libpgtcl.so.2.1
? src/interfaces/libpq/libpq.so.2.1
? src/interfaces/perl5/blib
? src/interfaces/perl5/Makefile
? src/interfaces/perl5/pm_to_blib
? src/interfaces/perl5/Pg.c
? src/interfaces/perl5/Pg.bs
? src/pl/plperl/blib
? src/pl/plperl/Makefile
? src/pl/plperl/pm_to_blib
? src/pl/plperl/SPI.c
? src/pl/plperl/plperl.bs
? src/pl/plpgsql/src/libplpgsql.so.1.0
? src/pl/tcl/Makefile.tcldefs
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.204
diff -c -r1.204 postgres.c
*** src/backend/tcop/postgres.c	2001/01/24 14:32:32	1.204
--- src/backend/tcop/postgres.c	2001/01/24 15:52:18
***************
*** 1791,1797 ****
  
  		EnableNotifyInterrupt();
  
! 		set_ps_display("idle");
  
  		/* Allow "die" interrupt to be processed while waiting */
  		ImmediateInterruptOK = true;
--- 1791,1799 ----
  
  		EnableNotifyInterrupt();
  
! 		if (!IsTransactionBlock())
! 			set_ps_display("idle");
! 		else	set_ps_display("idle in transaction");
  
  		/* Allow "die" interrupt to be processed while waiting */
  		ImmediateInterruptOK = true;
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.2
diff -c -r1.2 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample	2000/12/03 21:12:19	1.2
--- src/backend/utils/misc/postgresql.conf.sample	2001/01/24 15:52:18
***************
*** 18,20 ****
--- 18,196 ----
  # Any option can also be given as a command line switch to the
  # postmaster, e.g., 'postmaster -c log_connections=on'. Some options
  # can be set at run-time with the 'SET' SQL command.
+ 
+ 
+ 	enable_seqscan = true
+ 	enable_indexscan = true
+ 	enable_tidscan = true
+ 	enable_sort = true
+ 	enable_nestloop = true
+ 	enable_mergejoin = true
+ 	enable_hashjoin = true
+ 
+ 	ksqo = izer, false
+ 	geqo = true
+ 
+ 	tcpip_socket = false
+ 	ssl = false
+ 	fsync = true
+ 	silent_mode = false
+ 
+ 	log_connections = false
+ 	log_timestamp = false
+ 	log_pid = false
+ 
+ #ifdef USE_ASSERT_CHECKING
+ 	debug_assertions = true
+ #endif
+ 
+ 	debug_print_query = false
+ 	debug_print_parse = false
+ 	debug_print_rewritten = false
+ 	debug_print_plan = false
+ 	debug_pretty_print = false
+ 
+ 	show_parser_stats = false
+ 	show_planner_stats = false
+ 	show_executor_stats = false
+ 	show_query_stats = false
+ #ifdef BTREE_BUILD_STATS
+ 	show_btree_build_stats = false
+ #endif
+ 
+ 	trace_notify = false
+ 
+ #ifdef LOCK_DEBUG
+ 	trace_locks = false
+ 	trace_userlocks = false
+ 	trace_spinlocks = false
+ 	debug_deadlocks = false
+ #endif
+ 
+ 	hostname_lookup = false
+ 	show_source_port = false
+ 
+ 	sql_inheritance = true
+ 
+ 	{NULL, 0, NULL, false}
+ };
+ 
+ 
+ static struct config_int
+ ConfigureNamesInt[] =
+ {
+ 	geqo_threshold = PGC_USERSET,            &geqo_rels,
+ 	 DEFAULT_GEQO_RELS, 2, INT_MAX
+ 	geqo_pool_size = PGC_USERSET,            &Geqo_pool_size,
+ 	 DEFAULT_GEQO_POOL_SIZE, 0, MAX_GEQO_POOL_SIZE
+ 	geqo_effort = PGC_USERSET,            &Geqo_effort,
+ 	 1, 1, INT_MAX
+ 	geqo_generations = PGC_USERSET,            &Geqo_generations,
+ 	 0, 0, INT_MAX
+ 	geqo_random_seed = PGC_USERSET,            &Geqo_random_seed,
+ 	 -1, INT_MIN, INT_MAX
+ 
+ 	deadlock_timeout = PGC_POSTMASTER,         &DeadlockTimeout,
+ 	 1000, 0, INT_MAX
+ 
+ #ifdef ENABLE_SYSLOG
+ 	syslog = PGC_SIGHUP,             &Use_syslog,
+ 	 0, 0, 2
+ #endif
+ 
+ 	/*
+ 	 * Note: There is some postprocessing done in PostmasterMain() to
+ 	 * make sure the buffers are at least twice the number of
+ 	 * backends, so the constraints here are partially unused.
+ 	 */
+ 	max_connections = PGC_POSTMASTER,         &MaxBackends,
+ 	 DEF_MAXBACKENDS, 1, MAXBACKENDS
+ 	shared_buffers = PGC_POSTMASTER,         &NBuffers,
+ 	 DEF_NBUFFERS, 16, INT_MAX
+ 	port = PGC_POSTMASTER,         &PostPortNumber,
+ 	 DEF_PGPORT, 1, 65535
+ 
+ 	sort_mem = PGC_USERSET,            &SortMem,
+ 	 512, 1, INT_MAX
+ 
+ 	debug_level = PGC_USERSET,            &DebugLvl,
+ 	 0, 0, 16
+ 
+ #ifdef LOCK_DEBUG
+ 	trace_lock_oidmin = PGC_SUSET,              &Trace_lock_oidmin,
+ 	 BootstrapObjectIdData, 1, INT_MAX
+ 	trace_lock_table = PGC_SUSET,              &Trace_lock_table,
+ 	 0, 0, INT_MAX
+ #endif
+ 	max_expr_depth = PGC_USERSET,            &max_expr_depth,
+ 	 DEFAULT_MAX_EXPR_DEPTH, 10, INT_MAX
+ 
+ 	unix_socket_permissions = PGC_POSTMASTER,         &Unix_socket_permissions,
+ 	 0777, 0000, 0777
+ 
+ 	checkpoint_timeout",	PGC_POSTMASTER,			&CheckPointTimeout,
+ 	 300, 30, 1800
+ 
+ 	wal_buffers",			PGC_POSTMASTER,			&XLOGbuffers,
+ 	 8, 4, INT_MAX
+ 
+ 	wal_files",			PGC_POSTMASTER,			&XLOGfiles,
+ 	 0, 0, 64
+ 
+ 	wal_debug",			PGC_SUSET,				&XLOG_DEBUG,
+ 	 0, 0, 16
+ 
+ 	commit_delay",		PGC_USERSET,			&CommitDelay,
+ 	 5, 0, 1000
+ 
+     {NULL, 0, NULL, 0, 0, 0}
+ };
+ 
+ 
+ static struct config_real
+ ConfigureNamesReal[] =
+ {
+     effective_cache_size = PGC_USERSET,          &effective_cache_size,
+      DEFAULT_EFFECTIVE_CACHE_SIZE, 0, DBL_MAX
+     random_page_cost = PGC_USERSET,          &random_page_cost,
+      DEFAULT_RANDOM_PAGE_COST, 0, DBL_MAX
+     cpu_tuple_cost = PGC_USERSET,          &cpu_tuple_cost,
+      DEFAULT_CPU_TUPLE_COST, 0, DBL_MAX
+     cpu_index_tuple_cost = PGC_USERSET,          &cpu_index_tuple_cost,
+      DEFAULT_CPU_INDEX_TUPLE_COST, 0, DBL_MAX
+     cpu_operator_cost = PGC_USERSET,          &cpu_operator_cost,
+      DEFAULT_CPU_OPERATOR_COST, 0, DBL_MAX
+ 
+     geqo_selection_bias = PGC_USERSET,          &Geqo_selection_bias,
+      DEFAULT_GEQO_SELECTION_BIAS,   MIN_GEQO_SELECTION_BIAS, MAX_GEQO_SELECTION_BIAS
+ 
+     {NULL, 0, NULL, 0.0, 0.0, 0.0}
+ };
+ 
+ 
+ static struct config_string
+ ConfigureNamesString[] =
+ {
+ 	krb_server_keyfile = PGC_POSTMASTER,       &pg_krb_server_keyfile,
+ 	 PG_KRB_SRVTAB, NULL
+ 
+ 	unix_socket_group = PGC_POSTMASTER,       &Unix_socket_group,
+ 	 " = NULL
+ 
+ #ifdef ENABLE_SYSLOG
+ 	syslog_facility = PGC_POSTMASTER,	    &Syslog_facility, 
+ 	"LOCAL0 = check_facility	 
+ 	syslog_ident = PGC_POSTMASTER,	    &Syslog_ident, 
+ 	"postgres = NULL	 
+ #endif
+ 
+ 	unix_socket_directory",	  PGC_POSTMASTER,       &UnixSocketDir,
+ 	 " = NULL
+ 
+ 	virtual_host",			  PGC_POSTMASTER,		&VirtualHost,
+ 	 " = NULL
+ 
+ 	{NULL, 0, NULL, NULL, NULL}
+ };
+ 
+
Wed, 24 Jan 2001 10:53:43 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Small patch to replace 'idle' by 'trans' if
	transactionis still open