Re: How to diagnose application "hangs" in pg_receive?

Hi Kevin,

The tables are so small and the queries so simple, I am very sure its
not caused by bad shape of the database.
(and the queries run fast, iff they run)

I know that using those long-running transactions are very ugly,
however its a low-client-count app (2-5 clients) which all disconnect
at night where VACUUM and ANALYZE are performed once a week as cron
job.

Thanks, Clemens

2011/5/21 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>:
> Clemens Eisserer  wrote:
>
>> A hibernate based application I wrote suffers from "hangs" - the
>> thread which should fetch the data is stuck at pg_receive (the
>> function reading back data from the socket).
>
> You have two things happening which look bad, and could cause you to
> experience extreme bloat.  It may not be that your application is
> blocked, but that your database has gotten into such bad shape that
> queries are taking A Very Long Time.
>
> First, all those autovacuum processes which have been running for
> hours make me wonder if you're on a very old version of PostgreSQL
> -- we had some bugs for a while which might explain those if you
> haven't kept up with bug-fix minor releases:
>
> http://www.postgresql.org/support/versioning
>
> Please post the output from running the query on this page:
>
> http://wiki.postgresql.org/wiki/Server_Configuration
>
> Second, those " in transaction" connections which are hours
> old will prevent autovacuum (even if you don't have an old version
> with bugs) from cleaning up old versions of rows, making your
> queries progressively slower.  These would be caused by poor
> programming technique in the application or the application
> framework, and should be considered to be serious bugs.
>
> -Kevin
>
>
>