Skip site navigation (1) Skip section navigation (2)

Re: PreparedStatement clearParameters and setTimestamp

From: Kris Jurka <books(at)ejurka(dot)com>
To: Akira Sato <sato(dot)akira(at)oss(dot)ntt(dot)co(dot)jp>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: PreparedStatement clearParameters and setTimestamp
Date: 2006-07-25 18:57:23
Message-ID: Pine.BSO.4.63.0607251346190.1253@leary2.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

On Tue, 25 Jul 2006, Akira Sato wrote:

> This reply is about the following threads.
> http://archives.postgresql.org/pgsql-jdbc/2006-02/msg00156.php
>
> With case of setTimestamp(), it seem to always do "Parse".
> (setDate() and setTime() too)
>
> create table test(id int, tm timestamp);
>
> int n = 10;
> Timestamp tm = new Timestamp(System.currentTimeMillis());
> PreparedStatement pst = conn.prepareStatement("INSERT INTO test(id, tm)
> VALUES (?, ?)");
> for(int i=0; i< n; i++){
>    pst.clearParameters();
>    pst.setInt(1, i);
>    pst.setTimestamp(2, tm);
>    pst.executeUpdate();
> }
> pst.close()
>

The above problem has nothing to do with the clearParameters call.  You 
can see this by removing it from the above test and noting that it still 
parses it every time through.  The real problem has to do with using oid 
zero for the date+time datatypes.

When determining if a statement must be reparsed the driver checks that 
the parameters it currently has match with the previous execution.  This 
comparison is being done with the values returned from the server for the 
previous execution, not the values sent to the server.  So you parse once 
with oid 0 and it comes back with the oid of the resolved type, say 1184 
for timestamptz.  The next time through you again have oid 0 from 
setTimestamp, and it compares 0 == 1184? and it determines they don't 
match and it must be reparsed.

The attached patch fixes your test case for me, but I haven't applied it 
yet as I'd like to do some more testing.  It doesn't allows oid 0 to match 
any type the server resolves.

Kris Jurka

Attachment: dont-reparse-unspecified.patch
Description: text/plain (914 bytes)

In response to

Responses

pgsql-jdbc by date

Next:From: Mikko TiihonenDate: 2006-07-25 19:35:01
Subject: Query parsing speedup patch
Previous:From: Kris JurkaDate: 2006-07-25 18:27:17
Subject: Re: streaming bytea: is it possible ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group