Re: Very long time to commit or close connections

"Chris Smith" <cdsmith(at)twu(dot)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