Re: [HACKERS] JDBC prepared statements: actually not server prepared

(moved to pgsql-jdbc, as that's where JDBC development is usually discussed)

Henner Zeller wrote:
> Hi,
>
> While tracking down a query with a JDBC prepared statement, I
> noticed, that the statement actually wasn't prepared but sent to the
> server as 'normal' Statement. In my case, this led to a very slow query
> since the normal statement does not propagate the correct type -- in my
> case I queried an indexed coloumn of type 'int8', which
> was correctly passed to the PreparedStatement with setLong() -- however it
> was sent with a different type to the server (the
> AbstractJdbc1Statement::m_bindTypes[] are never used in this case).

I believe that 7.5 fixes the int4-vs-int8 issue. I assume you're running
a pre-7.5 server?

> The reason for this is, that by default, the PreparedStatement is not
> configured to use a server side prepared statement. I assume, this is
> unintended.

No, it's intentional.

In your case, it's actually not a PREPARE vs non-PREPARE issue at all;
it's a question of how the parameter values the driver generates are
interpreted by the query parser. Defaulting setUseServerPrepare to true
seems like the wrong solution for this. There are queries where use of
PREPARE will slow things down, and PREPARE against a 7.4 server does not
correctly return INSERT/UPDATE/DELETE rowcounts or resultset metadata
(against 7.5, I believe the rowcounts are correct but you still lose the
metadata).

IIRC, the latter problems should be detected by the driver's regression
tests. Did you rerun the tests with your change applied?

I have patches pending that replace use of PREPARE/EXECUTE with the v3
protocol's Parse/Bind messages (Parse/Bind are used for all queries, and
if server-side preparation is enabled then the queries are bound to
named statements and reused). The Bind message allows us to pass typed
parameters, bypassing the query-level parser (and associated type
inference) for the parameter values entirely. This should solve your
problem without the problems associated with PREPARE/EXECUTE.

-O