Re: Under what circumstances does PreparedStatement use stored

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
>