RE: Error in servlet - Mailing list pgsql-general

On Fri, 15 Sep 2000, Tim Kientzle wrote:

> The Statement variable here CANNOT be a class
> variable unless you're taking other steps to
> synchronize access.  Otherwise, you risk having
> two different threads trying to manipulate the
> same statement object at the same time.
OK, I followed your hint and made statement and resultset local
in all methods.

> The Connection object is what holds the connection
> to the database.  Whether or not that can safely
> be class scope depends on your particular JDBC driver
> and how you're using it.  This might work.
Hmm, I'm using the driver shipped with Debian which is from
ftp://ftp.postgresql.org/pub/postgresql.6.4.2.tar.gz

What about this?  Would it work.  Would anybody recommend the
driver from   http://www.retep.org.uk/postgres/ ?

> If you're not using auto-commit, this won't work,
> since each connection is a single transaction
> environment, and you'll have multiple transactions
> interfering with one another.
Could you explain this a little bit more detailed for a beginner
or is there any information source about this topic?

> Another ugly problem you'll encounter:  many database
> servers don't like long-lived connections, and will
> spontaneously drop them after a few hours.  At the very
> least, you should timestamp when you opened the connection
> (long timestamp = System.currentTimeMillis();)
> and close/reopen it every 30 minutes or so.  Also,
> you'll want to be sure to ping the connection regularly
> in case something goes down (like a bad network cable).
You speak about "many database servers".  What about PostgreSQL?

> If you have a relatively low-traffic site, opening
> one new connection for each request is not a real
> problem.  I've measured connection opens at around 0.1-0.2
> seconds on local MySQL and networked Oracle, which isn't at
> all prohibitive for a lot of applications.  Plus, that
> approach is easy to understand and very reliable.
I think in my case it would be best to hold the connection open
while performing the about 10 requests of the servlet which are
necessary to build my web-pages.

> If you have a higher-traffic site, look into connection
> pooling.  A good connection pool will cycle the connections,
> open more if you need them, and can deal with a lot of other
> issues as well.
Speaking about connection pooling I considered another problem:
I'm using JServ and it seems to try to open more than one connection
to the PostgreSQL server.  Formerly I used MS SQL server and there
where 5 open connections per servlet.
How do I check the open connections of a servlet to the PostgreSQL
server?  How do I enforce connection pooling?

Kind regards

            Andreas.