Re: Postgresql going to sleep?

I'm consistently getting into a situation where the database client
just goes to sleep in a query after some number of queries in quick
succession. Using PostgreSQL 7.2.3 on Red Hat Linux 8.0. The
postgres process shows up as ``idle in transaction'' in a ps.

Are there any tools I can use to figure out what's going on with the
client/server? pgmonitor doesn't give any additional information
(except ``no query is being executed'').

Regards,

-- Raju
--
Raj Mathur raju(at)kandalaya(dot)org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
All your domain are belong to us.
It is the mind that moves

Raj Mathur <raju(at)linux-delhi(dot)org> writes:
> I'm consistently getting into a situation where the database client
> just goes to sleep in a query after some number of queries in quick
> succession. Using PostgreSQL 7.2.3 on Red Hat Linux 8.0. The
> postgres process shows up as ``idle in transaction'' in a ps.

In that case, the backend is waiting for input from the client.

If the client thinks it is waiting for the backend, I'd wonder about a
missing "fflush" or something like that. What client software are you
using?

regards, tom lane

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> Raj Mathur <raju(at)linux-delhi(dot)org> writes:
>> I'm consistently getting into a situation where the database
>> client just goes to sleep in a query after some number of
>> queries in quick succession. Using PostgreSQL 7.2.3 on Red Hat
>> Linux 8.0. The postgres process shows up as ``idle in
>> transaction'' in a ps.

Tom> In that case, the backend is waiting for input from the
Tom> client.

Tom> If the client thinks it is waiting for the backend, I'd
Tom> wonder about a missing "fflush" or something like that. What
Tom> client software are you using?

Using Perl 5.8.0 and DBI 1.32. Any known problems with this setup?

-- Raju
--
Raj Mathur raju(at)kandalaya(dot)org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
All your domain are belong to us.
It is the mind that moves

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> Raj Mathur <raju(at)linux-delhi(dot)org> writes:
>> I'm consistently getting into a situation where the database
>> client just goes to sleep in a query after some number of
>> queries in quick succession. Using PostgreSQL 7.2.3 on Red Hat
>> Linux 8.0. The postgres process shows up as ``idle in
>> transaction'' in a ps.

Tom> In that case, the backend is waiting for input from the
Tom> client.

Tom> If the client thinks it is waiting for the backend, I'd
Tom> wonder about a missing "fflush" or something like that. What
Tom> client software are you using?

Thanks for the tip... I had a closer look at the client (did I mention
I was running Perl/DBI as a CGI?) and found that there's some issue in
the way Apache handles stderr prints from a CGI: if the CGI takes a
long time to execute and generates too much output on stderr, Apache
just puts it to sleep. I have all my SQL statements being printed to
the Apache log before execution, and after a while the log buffer or
whatever would just fill up and suspend the process.

Figuring out a workaround (better than the open STDERR,">/tmp/STDERR"
route :) for this, but the program is working as it should now. Or at
least working, trying to make it comply with the requirements :) At
least it's not a PgSQL problem.

Regards,

- -- Raju
- --
Raj Mathur raju(at)kandalaya(dot)org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
All your domain are belong to us.
It is the mind that moves
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)
Comment: Processed by Mailcrypt 3.5.6 and Gnu Privacy Guard <http://www.gnupg.org/>

iD8DBQE/dP/NyWjQ78xo0X8RAp7/AJ9SG2CDcRPwpubcnkHCYw6UpbfY7wCfe0ih
MMUEikdOJ8jMjKEUTDX/X5g=
=PjFn
-----END PGP SIGNATURE-----