Re: Under what circumstances does PreparedStatement use stored

You can call PGStatement.setUseServerPrepare() to cause the driver to
use a prepared plan for that statement.

--Barry

Alexander Staubo wrote:
> Afaik: As of 7.2, PostgreSQL now supports prepared plans. However, I do
> believe this requires explicit actions on part of the client (see
> documentation section 43.1.2).
>
> Unfortunately, PostgreSQL's JDBC driver does not support server-prepared
> statements yet. Every time you execute some statement, the statement's
> SQL text is sent in its entirety to the back end.
>
> (Batching statements does not help much, other than to reduce the number
> of client-server roundtrips.)
>
> Alexander.
>
> on 2004-04-09 16:51 James Robinson wrote:
>
>> 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
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)