Thread: postgresql idle

On Wed, Apr 21, 2004 at 09:12:27PM -0400, Jon Pastore wrote:
> This perl script is designed to handle payment posting for an application we
> developed.  It runs fine on our development server which is running apache
> 1.3.27 on ES 2.1
>
> on the production server the script hangs and we see the above msg in "ps
> aux"
>
> we wrote a wrapper for the program that will exec it from the command line
> and everything runs a-o-k
>
> has anyone else encounted something like this?  should I downgrade the
> installation of apache?

It's a Perl script, so why aren't you concerned about the Perl version?
If you can run it through the Perl debugger, you should be able to see
where it's hanging.  That's probably your best course of action.
Barring that, start putting some printf's in there to see where it's
hanging.

Michael
--
Michael Darrin Chaney
mdchaney@michaelchaney.com
http://www.michaelchaney.com/

Thank you for your response...and you are correct that is something to be
concerned about but after much frustration we found that pgsql thought there
was a transaction in progress and was waiting for it to complete when in
fact the commit had already taken place.

What we ended up doing was after a commit or rollback we would

undef $main::conn;

Forcing it to reestablish a new connection for the next itteration of the
loop.  This seemed to be an adequate solution but strangely we ran our test
and it failed as expected, implemented this change ran the test and it
worked, but then when we commented out the change and ran the test again and
it succeeded...so I have no idea.


Jon Pastore RHCE, President
IDE Tech, Inc.
(954) 360-0393 Office
(954) 428-0442 Fax
Public Key: http://www.idetech.net/keys/jpastore.asc

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Chaney
Sent: Thursday, April 22, 2004 10:26 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql idle

On Wed, Apr 21, 2004 at 09:12:27PM -0400, Jon Pastore wrote:
> This perl script is designed to handle payment posting for an
> application we developed.  It runs fine on our development server
> which is running apache
> 1.3.27 on ES 2.1
>
> on the production server the script hangs and we see the above msg in
> "ps aux"
>
> we wrote a wrapper for the program that will exec it from the command
> line and everything runs a-o-k
>
> has anyone else encounted something like this?  should I downgrade the
> installation of apache?

It's a Perl script, so why aren't you concerned about the Perl version?
If you can run it through the Perl debugger, you should be able to see where
it's hanging.  That's probably your best course of action.
Barring that, start putting some printf's in there to see where it's
hanging.

Michael
--
Michael Darrin Chaney
mdchaney@michaelchaney.com
http://www.michaelchaney.com/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Thu, Apr 29, 2004 at 02:04:47PM -0400, Jon Pastore wrote:
> pgsql thought there was a transaction in progress and was waiting
> for it to complete when in fact the commit had already taken place.

Come again?  That doesn't sound possible.  What ismore likely is that
somebody issues COMMIT;BEGIN; all at once.  You get one transaction
through, but you still end up idle in transaciton.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca

  I find that some clients (DBVisualizer for one) do exactly that -
execute the COMMIT;BEGIN sequence, and leaves idle
transactions on a consistent basis.

On Apr 29, 2004, at 3:19 PM, Andrew Sullivan wrote:

> On Thu, Apr 29, 2004 at 02:04:47PM -0400, Jon Pastore wrote:
>> pgsql thought there was a transaction in progress and was waiting
>> for it to complete when in fact the commit had already taken place.
>
> Come again?  That doesn't sound possible.  What ismore likely is that
> somebody issues COMMIT;BEGIN; all at once.  You get one transaction
> through, but you still end up idle in transaciton.
>
> A
>
> --
> Andrew Sullivan  | ajs@crankycanuck.ca
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com

On Thu, Apr 29, 2004 at 03:57:59PM -0400, Andrew Rawnsley wrote:
>
>  I find that some clients (DBVisualizer for one) do exactly that -
> execute the COMMIT;BEGIN sequence, and leaves idle
> transactions on a consistent basis.

Almost all the things I've see that set the autocommit behaviour will
also do this.  I suspect it's a pretty common approach.  It has
burned us at work several times, and I knew in advance it was a
problem.  I can't imagine what it's like for people who aren't aware
of it.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca

Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Thu, Apr 29, 2004 at 03:57:59PM -0400, Andrew Rawnsley wrote:
>> I find that some clients (DBVisualizer for one) do exactly that -
>> execute the COMMIT;BEGIN sequence, and leaves idle
>> transactions on a consistent basis.

> Almost all the things I've see that set the autocommit behaviour will
> also do this.  I suspect it's a pretty common approach.

Yeah.  We agreed in principle awhile back to "fix" this on the backend
side by postponing the actual transaction start until the first command
after BEGIN.  I looked at this just before 7.4 feature freeze, but
decided it wasn't quite trivial and I hadn't time to make it happen.
No one's gone back to work on it during the 7.5 cycle either.

Right now I'm not wanting to touch that code since both Alvaro and the
2PC guy have open patches against it...

            regards, tom lane

Is this a TODO?

---------------------------------------------------------------------------

Tom Lane wrote:
> Andrew Sullivan <ajs@crankycanuck.ca> writes:
> > On Thu, Apr 29, 2004 at 03:57:59PM -0400, Andrew Rawnsley wrote:
> >> I find that some clients (DBVisualizer for one) do exactly that -
> >> execute the COMMIT;BEGIN sequence, and leaves idle
> >> transactions on a consistent basis.
>
> > Almost all the things I've see that set the autocommit behaviour will
> > also do this.  I suspect it's a pretty common approach.
>
> Yeah.  We agreed in principle awhile back to "fix" this on the backend
> side by postponing the actual transaction start until the first command
> after BEGIN.  I looked at this just before 7.4 feature freeze, but
> decided it wasn't quite trivial and I hadn't time to make it happen.
> No one's gone back to work on it during the 7.5 cycle either.
>
> Right now I'm not wanting to touch that code since both Alvaro and the
> 2PC guy have open patches against it...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Thu, 29 Apr 2004, Andrew Rawnsley wrote:

>
>   I find that some clients (DBVisualizer for one) do exactly that -
> execute the COMMIT;BEGIN sequence, and leaves idle
> transactions on a consistent basis.
>

The 7.5 JDBC driver has been fixed to avoid this problem.

Kris Jurka

On Thu, Apr 29, 2004 at 09:54:08PM -0400, Bruce Momjian wrote:

> Tom Lane wrote:
> > Andrew Sullivan <ajs@crankycanuck.ca> writes:
> > > On Thu, Apr 29, 2004 at 03:57:59PM -0400, Andrew Rawnsley wrote:
> > >> I find that some clients (DBVisualizer for one) do exactly that -
> > >> execute the COMMIT;BEGIN sequence, and leaves idle
> > >> transactions on a consistent basis.
> >
> > > Almost all the things I've see that set the autocommit behaviour will
> > > also do this.  I suspect it's a pretty common approach.
> >
> > Yeah.  We agreed in principle awhile back to "fix" this on the backend
> > side by postponing the actual transaction start until the first command
> > after BEGIN.  I looked at this just before 7.4 feature freeze, but
> > decided it wasn't quite trivial and I hadn't time to make it happen.
> > No one's gone back to work on it during the 7.5 cycle either.
> >
> > Right now I'm not wanting to touch that code since both Alvaro and the
> > 2PC guy have open patches against it...

Actually, my patch is waiting for you to review it ;-)  On the other
hand, since I'm already touching that code, maybe I can include it in my
patch.  Or would you prefer to keep those things separate?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)

Alvaro Herrera wrote:
> > > Yeah.  We agreed in principle awhile back to "fix" this on the backend
> > > side by postponing the actual transaction start until the first command
> > > after BEGIN.  I looked at this just before 7.4 feature freeze, but
> > > decided it wasn't quite trivial and I hadn't time to make it happen.
> > > No one's gone back to work on it during the 7.5 cycle either.
> > >
> > > Right now I'm not wanting to touch that code since both Alvaro and the
> > > 2PC guy have open patches against it...
>
> Actually, my patch is waiting for you to review it ;-)  On the other
> hand, since I'm already touching that code, maybe I can include it in my
> patch.  Or would you prefer to keep those things separate?

Alvaro, can I ask what is left?  I know you have pg_subtrans, but what
plans do you have to abort subtransactions and bring the system back to
the state before the subtransaction started?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Thu, Apr 29, 2004 at 10:31:07PM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > > > Yeah.  We agreed in principle awhile back to "fix" this on the backend
> > > > side by postponing the actual transaction start until the first command
> > > > after BEGIN.  I looked at this just before 7.4 feature freeze, but
> > > > decided it wasn't quite trivial and I hadn't time to make it happen.
> > > > No one's gone back to work on it during the 7.5 cycle either.
> > > >
> > > > Right now I'm not wanting to touch that code since both Alvaro and the
> > > > 2PC guy have open patches against it...
> >
> > Actually, my patch is waiting for you to review it ;-)  On the other
> > hand, since I'm already touching that code, maybe I can include it in my
> > patch.  Or would you prefer to keep those things separate?
>
> Alvaro, can I ask what is left?

Several things.  I think I wrote them along with my previous patch.  The
visibility rules and the pg_clog protocol are what comes to mind
immediately.  This is the difficult part.

> I know you have pg_subtrans, but what plans do you have to abort
> subtransactions and bring the system back to the state before the
> subtransaction started?

Some of those things are already in place.  For example cursors are
closed/dropped, file deletions (DROP TABLE) no longer take place, file
creation is reverted, and the server is in a known state.  Some things
are missing: how to deal with deferred triggers, prepared statements,
locks, on-commit actions.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia está en otra parte" (Andre Breton)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Several things.  I think I wrote them along with my previous patch.  The
> visibility rules and the pg_clog protocol are what comes to mind
> immediately.  This is the difficult part.

Difficult part?  I think those are easy --- they are narrow and already
solved-in-principle problems.  What I do not understand is how you are
going to handle error recovery and undo in general.  Every single
backend module that has any at-abort or at-commit cleanup is going to
need work to extend its data structures to handle subtransactions.
That seems like a major mess :-(

            regards, tom lane

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
>> Yeah.  We agreed in principle awhile back to "fix" this on the backend
>> side by postponing the actual transaction start until the first command
>> after BEGIN.

> Actually, my patch is waiting for you to review it ;-)  On the other
> hand, since I'm already touching that code, maybe I can include it in my
> patch.  Or would you prefer to keep those things separate?

I'd opt for keeping it separate I think ...

            regards, tom lane