Thread: JDBC and servlet
We seem to be having an issue with the JDBC driver, Resin 2.0.5 servlet engine, and/or postgres 7.1.2. Basically what happens is this. In the Java Servlet code, the programmers are making an explicit close connection call to the database through the JDBC driver (As they should). Now! When the code us run from the command line, instead of through the servlet engine (Resin 2.0.5), everything works fine. The connection is made to the database, the SQL queries are run, the output is taken, and the connection to the DB is closed. But! When run through the servlet engine, the connection is never dropped, and we end up with these stale connections to the DB, and eventually, we cannot connect, since the older connections are never closed. Same code, same JVM, same database, but Resin sits in the middle. Resin isn't really doing anything other than passing the HTML output to Apache. I am also posting this to caucho.com incase it is a Resin issue, but if anyone has had this problem before, all help would be appreciated very much. Thank you! -James James Kelty Sr. Unix Systems Administrator Everbase Systems 541.488.0801 jamesk@everbase.net
James- We're using the Tomcat servlet engine & have had no similar problems, So I think you can eliminate the JDBC driver from your list & focus on Resin or something in your code. We also quickly decided that we needed to be using a connection pool for a web app. Opening & closing connections really drags your performance down quickly! Regards, -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of James Kelty > Sent: Friday, April 19, 2002 1:30 PM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] JDBC and servlet > > > We seem to be having an issue with the JDBC driver, Resin 2.0.5 servlet > engine, and/or postgres 7.1.2. > > Basically what happens is this. In the Java Servlet code, the programmers > are making an explicit close connection call to the database through the > JDBC driver (As they should). Now! When the code us run from the command > line, instead of through the servlet engine (Resin 2.0.5), > everything works > fine. The connection is made to the database, the SQL queries are run, the > output is taken, and the connection to the DB is closed. But! When run > through the servlet engine, the connection is never dropped, and we end up > with these stale connections to the DB, and eventually, we cannot connect, > since the older connections are never closed. Same code, same JVM, same > database, but Resin sits in the middle. Resin isn't really doing anything > other than passing the HTML output to Apache. > > I am also posting this to caucho.com incase it is a Resin issue, but if > anyone has had this problem before, all help would be appreciated > very much. > Thank you! > > -James > > James Kelty > Sr. Unix Systems Administrator > Everbase Systems > 541.488.0801 > jamesk@everbase.net > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Okay, maybe it is just me, but I think that something is wrong with the
way a plan is generated for the following update:
EXPLAIN UPDATE v SET nl=nl+1 WHERE id IN (SELECT sid FROM l WHERE did =
123456) ;
NOTICE: QUERY PLAN:
Seq Scan on v (cost=0.00..1884077041.93 rows=2873155 width=38)
SubPlan
-> Materialize (cost=327.85..327.85 rows=81 width=4)
-> Index Scan using l_pkey on l (cost=0.00..327.85 rows=81
width=4)
EXPLAIN
If I have static values in the IN(...) clause, it uses the 'v_pkey' index.
I know this because I have tried it. The only way to make this work the way
I want is to select all 'sid' from 'l' to my application server, then
build the update with static values, and execute it. For large data sets
(some 'did' have 20K+ 'sid'), it takes a while to download all the rows,
and then send it back. Also, there is a limitation somewhere around
10,000 values for the IN(...) clause which means the app server has to
send multiple UPDATEs.
I would think the planner could be smarter about this, especially given
that 'id' is the primary key for 'v', and 'l_pkey' is '(did, sid)'. So,
the planner should know that for any 'did', there will be no duplicate
'sid', and each 'sid' is tied to a specific 'id' in 'v'.
Alternatively, there might be a better way to write this query. Any
ideas? I can't think of any way to use EXISTS that wouldn't result in a
sequential scan of the data set.
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
Brian- I'm not sure if this will help the performance, but I believe this statement is equivalent: update v set nl=nl+1 where exists (select 'x' from l where l.sid = v.id and l.did = 123456); -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Brian McCane > Sent: Friday, April 19, 2002 3:55 PM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] Bad plan > > > > Okay, maybe it is just me, but I think that something is wrong with the > way a plan is generated for the following update: > > EXPLAIN UPDATE v SET nl=nl+1 WHERE id IN (SELECT sid FROM l WHERE did = > 123456) ; > > NOTICE: QUERY PLAN: > > Seq Scan on v (cost=0.00..1884077041.93 rows=2873155 width=38) > SubPlan > -> Materialize (cost=327.85..327.85 rows=81 width=4) > -> Index Scan using l_pkey on l (cost=0.00..327.85 rows=81 > width=4) > > EXPLAIN > > If I have static values in the IN(...) clause, it uses the 'v_pkey' index. > I know this because I have tried it. The only way to make this > work the way > I want is to select all 'sid' from 'l' to my application server, then > build the update with static values, and execute it. For large data sets > (some 'did' have 20K+ 'sid'), it takes a while to download all the rows, > and then send it back. Also, there is a limitation somewhere around > 10,000 values for the IN(...) clause which means the app server has to > send multiple UPDATEs. > > I would think the planner could be smarter about this, especially given > that 'id' is the primary key for 'v', and 'l_pkey' is '(did, sid)'. So, > the planner should know that for any 'did', there will be no duplicate > 'sid', and each 'sid' is tied to a specific 'id' in 'v'. > > Alternatively, there might be a better way to write this query. Any > ideas? I can't think of any way to use EXISTS that wouldn't result in a > sequential scan of the data set. > > - brian > > > Wm. Brian McCane | Life is full of doors that > won't open > Search http://recall.maxbaud.net/ | when you knock, equally > spaced amid those > Usenet http://freenews.maxbaud.net/ | that open when you don't > want them to. > Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber" > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >