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

Re: prepareThreshold=1 and statement.executeBatch() ??

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Joost Kraaijeveld <J(dot)Kraaijeveld(at)Askesis(dot)nl>
Cc: Kris Jurka <books(at)ejurka(dot)com>, "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: prepareThreshold=1 and statement.executeBatch() ??
Date: 2005-11-13 10:09:24
Message-ID: 437710D4.3080908@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
> On Sun, 13 Nov 2005, Joost Kraaijeveld wrote:
> 
>> I have a connection that is created with "prepareThreshold=1" in the
>> connection string. I use a prepared statement that I fill with
>> addbatch() and that I execute with executeBatch() (for full source: see
>> "application.java" attachment).
>>
>> LOG:  statement: PREPARE S_2 AS update prototype.customers set title=
>> $1 , defaultcurrency=$2, defaulttermsofdelivery=$3 ,
>> defaulttermsofpayment=$4 where customernumber=$5
>> LOG:  statement: <BIND>
>> LOG:  statement: EXECUTE <unnamed>  [PREPARE:  update
>> prototype.customers set title=$1 , defaultcurrency=$2, defaultter
>> msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5]
>> LOG:  duration: 773.841 ms
>> LOG:  statement: <BIND>
>> LOG:  statement: EXECUTE <unnamed>  [PREPARE:  update
>> prototype.customers set title=$1 , defaultcurrency=$2, defaultter
>> msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5]
>> LOG:  duration: 377.981 ms
>>
>> Does this output mean that the prepared statement with the name "S_2" is
>> not used in the following 2 EXECUTE statements and that therefor each
>> execute statement is planned again?

The driver does not actually issue PREPARE or EXECUTE statements; the 
server is pretending that the protocol-level Prepare/Bind/Execute 
messages are actually something issuing PREPARE/EXECUTE at the SQL level 
(but in reality, nothing is issuing precisely the queries that are being 
logged -- the query that is submitted is just your plain "update ..." 
query).

The PREPARE S_2 AS .. logs that a Prepare message was processed (for the 
query "update ..."). This does parsing/planning work and creates a named 
prepared statement called S_2 on the server.

The <BIND> means that some previously prepared statement (you can't tell 
which statement from what is logged! -- but it's S_2 in this case) is 
being bound to parameter values via a Bind message, creating an unnamed 
portal.

The EXECUTE <unnamed> means the unnamed portal is being executed via an 
Execute message. It also logs the underlying statement at that point, 
but not the statement name (!).

So if I read the logs right, the single prepared statement S_2 *is* 
being reused in the case above.

Yes, it's a horribly confusing way for the server to log things. I 
raised it on -hackers earlier in the 8.1 cycle, but I've not had time to 
work on it myself.

-O

In response to

Responses

pgsql-hackers by date

Next:From: Robert TreatDate: 2005-11-13 13:27:33
Subject: Re: MERGE vs REPLACE
Previous:From: Joost KraaijeveldDate: 2005-11-13 10:03:52
Subject: Re: prepareThreshold=1 and statement.executeBatch() ??

pgsql-jdbc by date

Next:From: Tom LaneDate: 2005-11-13 16:04:55
Subject: Re: prepareThreshold=1 and statement.executeBatch() ??
Previous:From: Joost KraaijeveldDate: 2005-11-13 10:03:52
Subject: Re: prepareThreshold=1 and statement.executeBatch() ??

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