Skip site navigation (1) Skip section navigation (2)

Under what circumstances does PreparedStatement use stored plans?

From: James Robinson <jlrobins(at)socialserve(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Under what circumstances does PreparedStatement use stored plans?
Date: 2004-04-09 14:51:52
Message-ID: 70300746-8A35-11D8-8620-000A9566A412@socialserve.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Subject pretty much tells it all -- under what circumstances does 
PreparedStatement use backend-based stored plans, and when are those 
plans removed from the database?

I'm using JBoss which uses PreparedStatement for all of its queries, 
and it uses its own datasource implementation. A typical page hit 
involves:

	1) connection yanked from the datasource, transaction started.

	2) Various CMP finders / accessor SQL statements run using that 
connection / transaction, but typically only one execution of a query 
for each distinct query pattern. Inbetween each bean method the pooled 
connection is closed, but I highly suspect that the same underlying 
connection must be returned to the next bean in line enrolled in the 
same transaction.

	3) session bean method completes, transaction committed, connection 
probably cleanly returned to the connection pool, but available to be 
yanked 'next time'. Idle connections are aged out periodically and 
finally closed.

 From watching the statement logs on the backend-side,  I don't believe 
that I'm getting any backend stored plans in action (using PG 7.4.2 and 
JDBC from CVS tip on devel boxes). Within a single transaction, the 
odds of JBoss preparing the same statement more than once are low, but 
across transactions, they are high, assuming the same session bean 
methods called, so there may be some benefit if somehow, magically, the 
same preparsed backend-plan from the previous run was chosen and used.

Or I may just be completely out of luck -- closing a PreparedStatement 
if/when backend cached-plans (can't remember their real name -- grr) 
are being used by the JDBC driver would then probably tell the backend 
to cleanup said plan, eh?

I love O/R.

----
James Robinson
Socialserve.com


Responses

pgsql-jdbc by date

Next:From: Barry LindDate: 2004-04-09 15:37:01
Subject: Re: Connection Idle in transaction
Previous:From: Rein ReezigtDate: 2004-04-09 14:37:06
Subject: cannot connect to db from remote machine

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group