Re: Very long time to commit or close connections

"Chris Smith" <cdsmith@twu.net> writes:
> We've got about a connection every 4 seconds being created to the database.
> Most of the time, this causes no problems at all.  Occasionally, though, with
> no apparent reason, the database will act up for some time (as little as 10
> minutes, or as long as 45 minutes).  During that time, it accepts connections
> and runs queries and statements with no problems, but hangs when closing the
> connections.  After the time period expires, the database will wake up and all
> connections that have been waiting will simultaneously return from the JDBC
> Connection.close() method.

There are not that many things happening during connection close.  Does
your app use temp tables?  LISTEN/NOTIFY?

> So right now, my question is this: does this sound familiar to anyone, and
> what information would you look for in troubleshooting this?

Nope ... how about looking at the contents of the pg_locks system view
when this happens?  What external conditions correspond to the instant
when the hangs are released?  (I do not believe that it just happens by
magic --- look for background cron jobs starting or finishing at that
time, eg a cron job that issues VACUUMs.)

What PG version is this, anyway?

            regards, tom lane