Thread: Under what circumstances does PreparedStatement use stored plans?

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

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
>

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@postgresql.org)

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?

In the CVS driver, it's controlled by calling a method on
org.postgresql.PGStatement on the prepared statement in question; by
default it's off.

For a patch that provides a bit more transparency try:

   http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00019.php

This patch allows you to specify a threshold at the datasource level
(via a property or URL parameter). PreparedStatement objects that are
reused more than the threshold begin to use server-side prepared queries
automatically. I put this patch together for a similar case to your
JBoss case -- where server-side prepared queries are useful, but
modifying the actual JDBC client code to call a postgresql-specific
method isn't easy.

Note that there are still a few corner cases where server-prepared
queries do not operate correctly in the face of query errors. See:

   http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00015.php.

-O

On Apr 9, 2004, at 7:04 PM, Oliver Jowett wrote:
>
> In the CVS driver, it's controlled by calling a method on
> org.postgresql.PGStatement on the prepared statement in question; by
> default it's off.
>
> For a patch that provides a bit more transparency try:
>
>   http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00019.php
>
> This patch allows you to specify a threshold at the datasource level
> (via a property or URL parameter). PreparedStatement objects that are
> reused more than the threshold begin to use server-side prepared
> queries automatically. I put this patch together for a similar case to
> your JBoss case -- where server-side prepared queries are useful, but
> modifying the actual JDBC client code to call a postgresql-specific
> method isn't easy.
>

That looks rather useful, but not as an island in and of itself (for
tricking out JBoss, anway). In JBoss's case, the PreparedStatement will
most likely get closed before an equivalent one gets created / used, so
some sort of longer-lived threshold would have to take control -- at
the connection level. Also at the connection level would be the set of
currently prepared queries, so that when JBoss calls perpareQuery(), we
could:

    1) Get the hashcode of the query to see if we've seen anything like it
before. Then use it to check a map of hashcode -> {threshold, prepared
query handle} (would hate to directly map the query string itself --
kiss my RAM goodbye -- but would have to stash the query string itself
for the the prepared queries to guarantee we're about to use the
correct plan).

    2) If the map's value does not have a query handle yet, increment the
hitcount on it and test against the threshold. If we pass the
threshold, then prepare query server-side and retain handle.

    3) If the map's value does have a query handle already (and the query
strings match exactly), then we're already planned, so use it.

So, ultimately, when JBoss checks out a connection from the datasource,
that connection may well already have a server-side prepared plan for
the query it is about to make, and/or increase the chances that this
query will be prepared in the future.

This sort of system would violate the contract of Statement.close(),
which should free up any resources, client or server side, associated
with this statement. But without it, I can't see how prepared
statements could ever really be used effectively in a pooled datasource
scenario outside of the occasional method that knows it is going to
fire off the same query many times in a loop. What I'd like to see use
prepared queries would be (at least) our finder methods that do many
joins -- a place where the planning cost might well be non-negligable.

Anyone know off the top of their heads any particular resource limits
server-prepared queries cost? Should any attempt at this scheme take
into account any particular upper-limit of prepared queries?

Perhaps something along the line of the N most-frequently used queries
should be prepared, once the use count crosses a threshold.
Ugh. One wonders if the optimization would be worth the effort involved
in coding as well as the runtime costs of tracking which queries are
parsed or not.

That threshold hack to prepared statement does indeed look crafty for
some cases though, just not mine, which, admittedly, is probably not
the general case. Is it considered for inclusion in the mainline
driver?

----
James Robinson
Socialserve.com

James Robinson <jlrobins@socialserve.com> writes:
> Anyone know off the top of their heads any particular resource limits
> server-prepared queries cost? Should any attempt at this scheme take
> into account any particular upper-limit of prepared queries?

There's no hard upper limit.  The cost is basically the backend memory
space needed to store the query parse and plan trees, which of course
depends quite a lot on the complexity of the query, but I'd think we'd
be talking a few kilobytes for typical queries.  So you could probably
store order-of-100 prepared plans without thinking about it, even in a
system with a lot of active backends.

            regards, tom lane

>
> There's no hard upper limit.  The cost is basically the backend memory
> space needed to store the query parse and plan trees, which of course
> depends quite a lot on the complexity of the query, but I'd think we'd
> be talking a few kilobytes for typical queries.  So you could probably
> store order-of-100 prepared plans without thinking about it, even in a
> system with a lot of active backends.
>
>             regards, tom lane
>

Heck then, it would be much easier then to plan 'em all, retain plans at
the driver scope, and when an equivalent prepared statement gets
prepared, automagically connect it up with the already-prepared
server handle. JBoss (in our observation, anyway), ages out old
connections, so these resources should ultimately get released.

The Map of unique query identifiers -> prepared plan handle would have
to be synchronized, as well as what the unique query identifier actually
would be would have to be thought out. Mapping the query pattern String
itself would in most cases be a vast memory leak, but would guarantee
uniqueness. The hashcode of the string doesn't claim to be 100% unique,
(although it seems to try hard to target unique numbers), so it'd be
out as
well. MD5'ing the string and saving the resulting byte array into a
nicely
hashable object might then be the way to go. I suppose this all assumes
that the lookup + maintenance of such a datastructure would ultimately
cost less than re-planning all queries all the time. If my database box
CPU
is lower than my appserver box's CPU, then I'm designing an optimization
which lacks a problem, aren't I? Or I'm bored with business-logic code.

----
James Robinson
Socialserve.com

James Robinson <jlrobins@socialserve.com> writes:
> ... I suppose this all assumes that the lookup + maintenance of such a
> datastructure would ultimately cost less than re-planning all queries
> all the time.

I think that is a safe bet to be true, as long as you get *some* mileage
out of the plan cache.  If the application issues a bunch of
no-two-alike queries then it's a loss of course.  But doesn't the JDBC
API distinguish prepared statements from unprepared ones?  ISTM it is
the app programmer's responsibility to prepare just those statements
he's likely to use more than once.  I don't think the driver need
second-guess this choice.

            regards, tom lane

[ forgot to cc list ]

On Apr 13, 2004, at 2:42 PM, Tom Lane wrote:

> But doesn't the JDBC
> API distinguish prepared statements from unprepared ones?  ISTM it is
> the app programmer's responsibility to prepare just those statements
> he's likely to use more than once.

The core JDBC API does not (AFAIKT). The closest difference is using
PreparedStatement as opposed to vanilla Statement, where the docs
for prepared statement reads:

    An object that represents a precompiled SQL statement.

      A SQL statement is precompiled and stored in a  PreparedStatement
        object. This object can then be used to  efficiently execute this
        statement multiple times.

Which seems to imply "backend prepare-me". Currently, Postgresql's
PreparedStatement doesn't, well, prepare, unless you downcast to
the implementation class and call a specific method. This may or may
not be planned to go away in the future, I do not know.

The "trouble" I'm in is one of middleware. JBoss runs all of its queries
through PreparedStatements on behalf of the lowly app-logic code
relatively unbeknownst to it. Some methods map out to SQL queries
every time, others sometimes, and others none, given the myriad of
config + application deployment options, the state of the JBoss object
cache, and the phase of the moon. But a common class of queries
produced by JBoss (and any EJB container) are known as 'finders',
and they typically involve joins, and they cannot be optimized away.
Typically, each finder invocation happens with a 'fresh' DB connection
yanked from the datasource pool of connections, and a new
PreparedStatement is created each invocation. If this were not
middleware-hosted, then your comment is dead-on, and is what the
JDBC specification writers implemented. I'm exploring a system-wide
optimization even-with our poisonous middleware piece.

Sometimes I envy you folks who get to live in the good old days
of "C" and GDB.

----
James Robinson
Socialserve.com

Tom Lane wrote:
> I think that is a safe bet to be true, as long as you get *some*
> mileage out of the plan cache.  If the application issues a bunch of
> no-two-alike queries then it's a loss of course.  But doesn't the JDBC
> API distinguish prepared statements from unprepared ones?  ISTM it is
> the app programmer's responsibility to prepare just those statements
> he's likely to use more than once.  I don't think the driver need
> second-guess this choice.

That's true; JDBC makes a distinction between prepared statements and
non-prepared statements.

From a practical matter, though, application developers often use
PreparedStatement when they don't plan to re-use the query.  This is primarily
because it adds to portability; using PreparedStatement.setDate to set a
parameterized date field in a query doesn't require the application developer
to know the proper String representation of the date (which differs, for
example, with Access as the database).

So on one hand, the distinction isn't made so cleanly in the real world.  On
the other hand, there's a compelling argument that a developer who uses
PreparedStatement for a one-shot query simply to avoid handling portability in
a more comprehensive way should expect poor optimization behavior on behalf of
the database.

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

James Robinson wrote:

[... maintaining a cache of prepared queries ...]

> So, ultimately, when JBoss checks out a connection from the datasource,
> that connection may well already have a server-side prepared plan for
> the query it is about to make, and/or increase the chances that this
> query will be prepared in the future.
>
> This sort of system would violate the contract of Statement.close(),
> which should free up any resources, client or server side, associated
> with this statement. But without it, I can't see how prepared statements
> could ever really be used effectively in a pooled datasource scenario
> outside of the occasional method that knows it is going to fire off the
> same query many times in a loop. What I'd like to see use prepared
> queries would be (at least) our finder methods that do many joins -- a
> place where the planning cost might well be non-negligable.

This sounds like JDBC3's "statement pooling" option. There's no API for
this, it's just allowable behaviour, so we should be fine to implement
something like this.

Actually, you don't have to do this in the driver itself at all -- you
can do it in your connection pooling layer if you proxy the
PreparedStatement objects handed out to clients. close() on a proxy
returns the underlying statement to a per-physical-connection pool of
statements rather than actually closing it. Then the driver can just
store prepared-query-plan info per PreparedStatement as it currently does.

-O

Tom Lane wrote:
> James Robinson <jlrobins@socialserve.com> writes:
>
>>... I suppose this all assumes that the lookup + maintenance of such a
>>datastructure would ultimately cost less than re-planning all queries
>>all the time.
>
>
> I think that is a safe bet to be true, as long as you get *some* mileage
> out of the plan cache.  If the application issues a bunch of
> no-two-alike queries then it's a loss of course.  But doesn't the JDBC
> API distinguish prepared statements from unprepared ones?  ISTM it is
> the app programmer's responsibility to prepare just those statements
> he's likely to use more than once.  I don't think the driver need
> second-guess this choice.

The problem is that JDBC's PreparedStatement provides two things:
repeated execution of the same query with different parameters, and
portable parameterization of queries. So it's likely that many one-shot
or infrequently executed queries will still use a PreparedStatement.

This is why a threshold on PreparedStatement reuse before using
PREPARE/EXECUTE seemed like a good idea -- we should be able to avoid
PREPARE-ing the one-shot queries, at a minimum.

-O

On Apr 13, 2004, at 5:18 PM, Oliver Jowett wrote:

> This sounds like JDBC3's "statement pooling" option. There's no API
> for this, it's just allowable behaviour, so we should be fine to
> implement something like this.
>
> Actually, you don't have to do this in the driver itself at all -- you
> can do it in your connection pooling layer if you proxy the
> PreparedStatement objects handed out to clients. close() on a proxy
> returns the underlying statement to a per-physical-connection pool of
> statements rather than actually closing it. Then the driver can just
> store prepared-query-plan info per PreparedStatement as it currently
> does.

I fear diving into the JBoss connection pool code. The JDBC driver is a
shining example of clear, simple code relative to the JBoss codebase.
Plus I would imagine this issue would exist across any EJB container /
connection pool mechanism.

I'll need to read the JDBC3 spec, I guess.

----
James Robinson
Socialserve.com

On Apr 13, 2004, at 5:18 PM, Oliver Jowett wrote:

> This sounds like JDBC3's "statement pooling" option. There's no API
> for this, it's just allowable behaviour, so we should be fine to
> implement something like this.

OK -- I read through the JDBC3 spec, and section 11.6 covers pooled
statements. Highlights include:

    "If a pooled connection reuses statements, the reuse must be
completely transparent to an application. In other words, from the
application’s point of view, using a PreparedStatement object that
participates in statement pooling is exactly the same as using one that
does not. Statements are kept open for reuse entirely under the covers,
so there is no change in application code. If an application closes a
PreparedStatement object, it must still call
Connection.prepareStatement in order to use it again. The only visible
effect of statement pooling is a possible improvement in performance."

    "An application may find out whether a data source supports statement
pooling by calling the DatabaseMetaData method
supportsStatementPooling."

    "In FIGURE 11-2 [ diagram of PooledConnection instances managing their
own pool of PreparedStatement objects ], the connection pool and
statement pool are implemented by the application server. However, this
functionality could also be implemented by the driver or underlying
data source. This discussion of statement pooling is meant to allow for
any of these implementations."

    Do I properly infer from this last statement that the pool of
PreparedStatements could be implemented by the app-server's connection
pool OR the JDBC driver itself (where I can do it reasonably) or in the
backend SQL server itself (which would be wicked voodoo indeed)?

    Funny, it gives no hints how to efficiently recognize if an equivalent
statement is being prepared for the Nth time.

    I'm gonna go for it. Sounds like a fun project.

----
James Robinson
Socialserve.com

On Apr 13, 2004, at 5:18 PM, Oliver Jowett wrote:

> This sounds like JDBC3's "statement pooling" option. There's no API
> for this, it's just allowable behaviour, so we should be fine to
> implement something like this.

Diving in, I see that Postgres's PREPARE statement needs the types
up-front, as in:

    PREPARE t_prep (TEXT) as select id from users where name = $1;

But PreparedStatement doesn't know the JDBC datatypes (which we map onto
Postgres types) until execute time. Hence I guess
AbstractJDBC1Statement's
delaying of server-preparing until execute-time, when all of the params
have
been pushed in.

A global cache of prepared PreparedStatements seems to have to assume
that
subsequent calls ought to be executed with the same param types. Is
this too
bold of an assumption -- I know that in my EJB code this will hold, but
I don't
know about others.

Unless the key to the map of cached prepared statement also
incorporates the
type signatures of each param. Ugh. That would push fetching from the
map
until the actual executeQuery() call on PreparedStatement, as opposed to
at Connection.prepareStatement(String queryPattern) time -- which I
can semi-gracefully hook into in AbstractJdbc3Connection.java to
keep JDBC1/2 unaffected. The executeQuery() hooking looks harder to
implement without poisoning JDBC 1/2.

Unrelated, what is the philosophy regarding putting what type of code in
the AbstractXXX class as opposed to the JDBCNXXX class? I'm sure there's
a good reason, but it escapes my grasp at the moment ...

----
James Robinson
Socialserve.com

James Robinson wrote:
>
> On Apr 13, 2004, at 5:18 PM, Oliver Jowett wrote:
>
>> This sounds like JDBC3's "statement pooling" option. There's no API
>> for this, it's just allowable behaviour, so we should be fine to
>> implement something like this.
>
>
> Diving in, I see that Postgres's PREPARE statement needs the types
> up-front, as in:
>
>     PREPARE t_prep (TEXT) as select id from users where name = $1;
>
> But PreparedStatement doesn't know the JDBC datatypes (which we map onto
> Postgres types) until execute time. Hence I guess AbstractJDBC1Statement's
> delaying of server-preparing until execute-time, when all of the params
> have
> been pushed in.

Yes.

In actual fact this is probably broken in the current driver -- it
should discard any existing server-side prepared query if the parameter
types change, but currently I don't think it does.

> A global cache of prepared PreparedStatements seems to have to assume that
> subsequent calls ought to be executed with the same param types. Is this
> too
> bold of an assumption -- I know that in my EJB code this will hold, but
> I don't
> know about others.

> Unless the key to the map of cached prepared statement also incorporates
> the type signatures of each param. Ugh.

You can probably get away with ignoring the parameter types altogether
and just key on (query,resultset type,resultset holdability). If the
actual parameter types change (i.e. you gave out a pooled statement with
the right query but the "wrong" types) then you don't get the benefit of
PREPARE, but you do still get correct behaviour (assuming the underlying
statement behaves correctly, see above).

> That would push fetching from the map
> until the actual executeQuery() call on PreparedStatement, as opposed to
> at Connection.prepareStatement(String queryPattern) time -- which I
> can semi-gracefully hook into in AbstractJdbc3Connection.java to
> keep JDBC1/2 unaffected. The executeQuery() hooking looks harder to
> implement without poisoning JDBC 1/2.

I'd leave the core driver alone and look at implementing the pooling in
the ConnectionPoolDataSource layer. There are already proxy Connection
and Statement objects created by PooledConnectionImpl; you should be
able to intercept Connection.prepareStatement() and Statement.close() to
manage the statement pool. The pool itself ends up attached to a
PooledConnectionImpl.

> Unrelated, what is the philosophy regarding putting what type of code in
> the AbstractXXX class as opposed to the JDBCNXXX class? I'm sure there's
> a good reason, but it escapes my grasp at the moment ...

The AbstractJdbcN classes have code that:

   a) can be compiled under JDBC version N or above
   b) isn't a concrete class

They are compiled for all JDBC versions >= N.

The JdbcN classes are thin wrappers that provide the actual
implementation of the JDBC interfaces for a specific JDBC version M.
They are compiled for JDBC version M only.

The reason for this split is that if you have a concrete class that
implements only the JDBC1 versions of the JDBC interfaces, it's not
going to compile under JDBC2 as the extra parts of the JDBC interfaces
present in JDBC2 are not implemented. But we want to share as much code
as possible between JDBC versions, and we can't just use the same code
under all versions as (for example) there are extra constants and
interfaces present only in JDBC3 that JDBC3-implementing code must
reference.

So we make the main implementation classes abstract, have a hierarchy of
classes that implements the various JDBC levels, and "tap off" the
appropriate level as a concrete implementation depending on the version
of JDBC in use when compiling.

-O

On Apr 13, 2004, at 10:22 PM, Oliver Jowett wrote:

> I'd leave the core driver alone and look at implementing the pooling
> in the ConnectionPoolDataSource layer. There are already proxy
> Connection and Statement objects created by PooledConnectionImpl; you
> should be able to intercept Connection.prepareStatement() and
> Statement.close() to manage the statement pool. The pool itself ends
> up attached to a PooledConnectionImpl.

Sounds more than reasonable. Overriding close() in
AbstractJdbc3Statement to perform "less" if it was pooled was looking
ugly at best.


----
James Robinson
Socialserve.com

James Robinson <jlrobins@socialserve.com> writes:
> Diving in, I see that Postgres's PREPARE statement needs the types
> up-front, as in:
>     PREPARE t_prep (TEXT) as select id from users where name = $1;

Yes, but that is not the facility you want to use.  The facility that
was specifically designed to support JDBC is the V3-protocol prepare/
bind/execute message group.  That stuff can push back inferred parameter
types, which is what I think you are looking for.  See
http://www.postgresql.org/docs/7.4/static/protocol.html
particularly
http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52666
There's also a bunch of discussion in the mail list archives from about
a year ago.

            regards, tom lane

On Apr 13, 2004, at 11:04 PM, Tom Lane wrote:

> The facility that
> was specifically designed to support JDBC is the V3-protocol prepare/
> bind/execute message group.

Aha. I see. Looks like this is a somewhat separate / lower level problem
from what I had set out to tackle. I see a note in the JDBC driver as it
chooses to either use cursors or server-side prepare:

// We prefer cursor-based-fetch over server-side-prepare here.
// Eventually a v3 implementation should let us do both at once.

The current driver, if asked to use server-side prepares and
not also cursors then currently does indeed issue the
PREPARE, EXECUTE, and DEALLOCATE commands at the
SQL level.

So, making that path v3-aware could:
    1) Allow for both cursor-based fetching and prepared
statements.
    2) Allow for late-binding of param-types to a prepared
statement, fixing the now-ignored problem of what if the
Nth reuse of a current JDBC server-planned prepared
statement binds types other than what was passed-in
on the first invocation.

This sort of fix belongs down deep in AbstractJDBC1Statement,
whereas my hack-ish cache would exist at a layer much higher
than this, when I follow Oliver's advice and cache PreparedStatements
at the PooledConnection level.

Thanks for the education, as always!

----
James Robinson
Socialserve.com

James Robinson <jlrobins@socialserve.com> writes:
> This sort of fix belongs down deep in AbstractJDBC1Statement,
> whereas my hack-ish cache would exist at a layer much higher
> than this,

I didn't say it was easy ;-) ... when we were designing this last year,
Dave gave me to understand that actually using it will take some pretty
significant revisions in the JDBC driver.  But the way forward is open,
as far as I know.

            regards, tom lane

>>
>
> I didn't say it was easy ;-) ... when we were designing this last year,
> Dave gave me to understand that actually using it will take some pretty
> significant revisions in the JDBC driver.  But the way forward is open,
> as far as I know.
>
>             regards, tom lane
>

I've made it through giving a thorough reading of the v3 messages
Parse, Bind, and Execute, and it does indeed look like things were
planned out enough to map relatively cleanly onto the variations
required by a full JDBC implementation (at least the cursor-
based fetching and/or prepared queries -- You don't want
to know about updateable result sets, do you :-). Looks like
the existing driver 'shells out' to SQL commands in these
places, needing to be replaced with lower-level raw protocol
commands (assuming a v3-capable backend, OC).

Not naively done, but also not uber-hacker material either.
Not sure if I'm stepping up to the task (right away) -- looks
like I can get my hack-cache done first, assuming that
PreparedStatement should just 'work', and if it doesn't, then it
could be corrected in parallel or after the fact.

Anything else looks fun relative to EJB / web-application code.

----
James Robinson
Socialserve.com