Thread: JDBC and servlet

JDBC and servlet

From
"James Kelty"
Date:
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


Re: JDBC and servlet

From
"Nick Fankhauser"
Date:
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
>


Bad plan

From
Brian McCane
Date:
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"


Re: Bad plan

From
"Nick Fankhauser"
Date:
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
>