Thread: Postgresql going to sleep?

Postgresql going to sleep?

From
Raj Mathur
Date:
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@kandalaya.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

Re: Postgresql going to sleep?

From
Tom Lane
Date:
Raj Mathur <raju@linux-delhi.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

Re: Postgresql going to sleep?

From
Raj Mathur
Date:
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Raj Mathur <raju@linux-delhi.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@kandalaya.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

Re: Postgresql going to sleep?

From
Raj Mathur
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Raj Mathur <raju@linux-delhi.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@kandalaya.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-----