
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).

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.

I fixed this by adding this to my local version (current CVS):

------------------
--- org/postgresql/jdbc3/Jdbc3PreparedStatement.java    29 Mar 2004 19:17:12 -0000      1.6
+++ org/postgresql/jdbc3/Jdbc3PreparedStatement.java    20 Jun 2004 11:54:00 -0000
@@ -13,6 +13,7 @@
        public Jdbc3PreparedStatement(Jdbc3Connection connection, String
sql) throws SQLException
        {
                super(connection, sql);
+                setUseServerPrepare(true);
        }

        public BaseResultSet createResultSet (Field[] fields,
java.util.Vector tuples, String status, int updateCount, long insertOID)
throws SQLException
--------------
(same for Jdbc2PreparedStatement and Jdbc1PreparedStatement).

If this is the right way to do ? And if so .. could someone apply
this patch ?

Thanks,
 -hen
Bücher kaufen und Freie Software fördern | http://bookzilla.de/

Sun, 20 Jun 2004 14:09:29 +0200 (CEST)
Henner Zeller <henner@freiheit.com>
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

Mon, 21 Jun 2004 01:54:49 +1200
Oliver Jowett <oliver@opencloud.com>
Re: [HACKERS] JDBC prepared statements: actually not server prepared
Please post this to the jdbc list.

Dave
On Sun, 2004-06-20 at 08:09, 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).
> 
> 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.
> 
> I fixed this by adding this to my local version (current CVS):
> 
> ------------------
> --- org/postgresql/jdbc3/Jdbc3PreparedStatement.java    29 Mar 2004 19:17:12 -0000      1.6
> +++ org/postgresql/jdbc3/Jdbc3PreparedStatement.java    20 Jun 2004 11:54:00 -0000
> @@ -13,6 +13,7 @@
>         public Jdbc3PreparedStatement(Jdbc3Connection connection, String
> sql) throws SQLException
>         {
>                 super(connection, sql);
> +                setUseServerPrepare(true);
>         }
> 
>         public BaseResultSet createResultSet (Field[] fields,
> java.util.Vector tuples, String status, int updateCount, long insertOID)
> throws SQLException
> --------------
> (same for Jdbc2PreparedStatement and Jdbc1PreparedStatement).
> 
> If this is the right way to do ? And if so .. could someone apply
> this patch ?
> 
> Thanks,
>  -hen
> Bï¿½cher kaufen und Freie Software fï¿½rdern | http://bookzilla.de/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 
> 
> 
> !DSPAM:40d5815f68141026638008!
> 
> 
-- 
Dave Cramer
519 939 0336
ICQ # 14675561


Thu, 24 Jun 2004 11:02:04 -0400
Dave Cramer <pg@fastcrypt.com>
Re: JDBC prepared statements: actually not server