Re: 500 'too many non super user connections'. Guilty parameter: 'statement_timeout = 0'

Enric,

I've noticed a few things that may be major problems, IMHO:

1 - If you're using JSP/Tomcat with postgresql, than you, at
least, should consider using a connection pool. You simply
can't predict the number of pageviews being requested by
web users. Period. If every page spawns a new database connection,
soon or later you're doomed.

2 - You made clear that you're dealing with legacy code, but,
in using java, the programmer should code database access like
this:
try {

  get_db_connection();
  do_some_queries();

} catch {
  deal_exceptions();
} finally {
  close_db_connection();
}

I'm not an expert in computer programming. Maybe you can think
better ways to do the same thing, but, at the end of the process,
the database connection should be closed. Otherwise you'll get
leaked connections.

3 - We have a similar setup here. But our database was being
accessed from different perspectives:
 - a JSP page for report generation
 - normal, application specific data handling, storage and retrival
 - human beings checking data and managing the database

One thing in common: everyone was connecting from the same machine
running the database (it is also a web/jsp server), with the same
user. We noticed some connection leaks and, despite I could link
the leakage with apache/jsp/tomcat (more on that later), we agreed that in
other
circumstances we simply could not be able to
determine where it was coming from. Quick solution: one database,
multiple users, so we can track what process have spawned the
db connection more easily.

4 - connection leaks from JSP

Application servers normally are set up with very specific memory
and garbage collection tuning.
In java, garbage collection has a bit of undesirability, since it
freezes the entire VM during its own execution.
Maybe your JSP pages are leaking connections because the GC isn't
running too often as you desire. Since your server isn't a very
busy one, increasing the number of garbage collection runnings should
close the undesired connections without touching legacy code.

5 - server reliability

You stated that servers should work despite client behaviour or
configuration. I respectfully disagree with that. Well, I even
don't think your issue with the number of database connections
is a problem after all. Postgres was able to determine that more
people were trying to connect to it than what was speficied for
it to handle. During this entire process, all your data were
safe, all other active connections were being serviced.
So, what's the issue here? If the number of connections were set
to "infinite", you really could get into a Denial Of Service issue.
That's why hard limits are useful. They are not mistakes or errors,
but part of the process.

That's all I had to say... Sorry for not including your original
message, but I think this email is already too long to read without
configuration files and commands output.


Marcus.