Thread: setUseServerPrepare(true) and executeQuery(String)

playing with the new server side prepared statements of 7.3 I discovered
that _all_ statements will be prepared as server objects, not only
the prepared ones. Depending on the application this might result in
a permanently increasing number of server objects.

In executeQuery(String), AbstractJdbc1Statement.java:141
I would suggest the following modification:

    boolean old_useServerPrepare = m_useServerPrepare;
    m_useServerPrepare = false;     // turn off for non-prepared statements
    java.sql.ResultSet rs = executeQuery();
    m_useServerPrepare = old_useServerPrepare;
    return rs;

regards,
Harald.

Harald,

Why would you want to disable caching for standard jdbc statements
plans?  In my application these are the ones that get reused the most,
and therefore benefit most from caching the plan on the server.  If you
don't wan't to use a server side prepared and cached plan, then don't
enable it for your regular jdbc statement objects.

thanks,
--Barry

Harald Krake wrote:
> playing with the new server side prepared statements of 7.3 I discovered
> that _all_ statements will be prepared as server objects, not only
> the prepared ones. Depending on the application this might result in
> a permanently increasing number of server objects.
>
> In executeQuery(String), AbstractJdbc1Statement.java:141
> I would suggest the following modification:
>
>     boolean old_useServerPrepare = m_useServerPrepare;
>     m_useServerPrepare = false;     // turn off for non-prepared statements
>     java.sql.ResultSet rs = executeQuery();
>     m_useServerPrepare = old_useServerPrepare;
>     return rs;
>
> regards,
> Harald.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

On Sunday 01 December 2002 09:43 am, Barry Lind wrote:
> Why would you want to disable caching for standard jdbc statements
> plans?
> If you don't wan't to use a server side prepared and cached plan,
> then don't enable it for your regular jdbc statement objects.

Hi Barry,

this means that I have to put postgres-specific java code into my
application. I don't like that, because jdbc is an _abstraction_ layer and
already provides two query types: prepared and non-prepared.
If the query is a one time shot: use executeQuery(String).
If you're able to factor out parameters from the query string and/or there's
a potential for it getting reused: prepare it and use executeQuery().
So, according to the jdbc api it's up to the java programmer to decide
which statements get prepared and which are one time shots. There is
no need for an extra method, especially a dbms-specific one.
Consequently, the postgres-jdbc driver should prepare a server object
in the prepareStatement()-method and not in executeQuery() as
it is now.
At least, this is how the jdbc api was designed and what a java programmer
expects.
It's fine to provide an extra switch that turns on server side prepares
even for non-prepared queries, but this should be an option (best in the url)
cause it's a non-standard behaviour that would badly waste resources
in some applications (imagine a typical query-by-forms app).

Harald.