Re: Any way to *not* use server-side prepared statements in Postgresql?

On Mon, Jul 18, 2011 at 7:50 PM, Oliver Jowett <oliver@opencloud.com> wrote:
> On 19 July 2011 14:07, Yang Zhang <yanghatespam@gmail.com> wrote:
>
>> However, in Java, issuing:
>>
>>    conn.createStatement().executeQuery("select * from account where
>> id = '00100000006ONCrAAO'");
>
> You probably want to be using a PreparedStatement here anyway (it's
> just good practice to let the driver worry about parameter value
> escaping etc, especially given that the rules are a bit variable
> depending on server version)
>
>> results in:
>>
>>    2011-07-18 18:44:59 PDT LOG:  duration: 4.353 ms  parse <unnamed>:
>> select * from account where id = '00100000006ONCrAAO'
>>    2011-07-18 18:44:59 PDT LOG:  duration: 0.230 ms  bind <unnamed>:
>> select * from account where id = '00100000006ONCrAAO'
>>    2011-07-18 18:44:59 PDT LOG:  duration: 0.246 ms  execute
>> <unnamed>: select * from account where id = '00100000006ONCrAAO'
>>
>> Some searching shows that the PG JDBC driver *always* uses prepared
>> statements:
http://postgresql.1045698.n5.nabble.com/JDBC-prepared-statements-amp-server-side-prepared-statements-td1919506.html
>>
>> Is there any way to circumvent server prepared statements? If it makes
>> a difference, I'm asking regarding PG 8.4 and 9.0. Thanks in advance.
>
> You can use the v2 protocol (which inserts parameter values as text,
> rather than sending them out of line), but you will lose various other
> bits of driver functionality that depend on the v3 protocol.

Hmm...this sounded promising until I found that I'm using Python
psycopg2.4. They wrap libpq and dropped v2 protocol support by
psycopg2.3 (http://wiki.postgresql.org/images/7/77/Psycopg-2010-stuttgart.pdf),
yet the statements that are being issued still appear as above. I'll
still give the v2 protocol a shot, but something is inconsistent here.

>
> Perhaps a better question is: Why do you want to avoid server prepared
> statements?
> If it is because you want more compact logging, perhaps there is
> something that can be improved on the server side?

It's not for logging and we don't avoid prepared statements - we
typically use them, but for one particular workload they're causing a
slowdown. It's for situations where we're issuing many queries and few
queries share the same structure.

--
Yang Zhang
http://yz.mit.edu/