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

parametrized statements, but always replan?

From: Kevin Murphy <murphy(at)genome(dot)chop(dot)edu>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: parametrized statements, but always replan?
Date: 2006-12-20 19:44:22
Message-ID: 45899296.9040008@genome.chop.edu (view raw or flat)
Thread:
Lists: pgsql-jdbc
Sorry for the newbie question here.  The documentation 
(documentation/81/server-prepare.html) isn't clear about this point.  
I'm using PG 8.1 with the matching jdbc driver.

Will the following code execute the query with a custom plan based on 
the value of var (assuming no use of the prepare threshold)?

        prestmt = conn.prepareStatement(query);
        prestmt.setString(1, var);
        resultSet = prestmt.executeQuery();
        // ... use result set ...
        resultSet.close();
        prestmt.close();

I'd like the safety of parametrized SQL statements combined with the 
greater performance (for certain queries) of having every statement 
planned based on the specific parameters.  Ideally, I'd like to use 
prepareStatement() and executeQuery() with out-of-band parameters, but 
have the finished statement be custom-planned.  Most of my queries 
wouldn't benefit from the "plan once, execute many times" model.

I've read documentation/81/server-prepare.html, but somehow it wasn't 
clear.  It makes it sound as if by default, server-side prepared 
statements are not really used ("There are a number of ways to enable 
server side prepared statements ..."), which would be good for me.

But looking at my postgresql log, that doesn't seem to be the case; I 
see PREPARE, BIND, and EXECUTE lines for each query.

Thanks,
Kevin Murphy


Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2006-12-20 20:07:22
Subject: Re: parametrized statements, but always replan?
Previous:From: John LHDate: 2006-12-20 18:22:22
Subject: Driver Bug

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