Prepared Statements

straight from jdbc2.1 doc
http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html
Statement
Statement object to submit a set of heterogeneous update commands together
as a single unit, or batch, to the underlying DBMS
i.e. execute Statement without parameters
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html

PreparedStatement
An element in a batch consists of a parameterized command and an associated
set of parameters when a PreparedStatement is used. (i.e. A Statement with
params)
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

CallableStatement
Multiple sets of input parameter values may be associated with a callable
statement and sent to the DBMS together.
(For procedures)
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html

Viel Gluck
Martin
----- Original Message -----
From: <mljv@planwerk6.de>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, January 09, 2008 2:02 PM
Subject: [GENERAL] Prepared Statements

Hi,

i am trying to understand "Prepared Statements". I am asking because i

want to

understand the impact of "Prepared statements" to my application.

Actually i use Hibernate, DBCP Connection Pool with Postgresql-JDBC Driver

and

Postgresql 8.1.

- I know there is a PREPARE Statement in Postgresql and read the docs.
- in PostgresqlJDBC i have a prepareThreshold parameter which i left to
default of 5.
- in DBCP i have a property "poolPreparedStatements", set to true. Does

ist

just configure prepareThreshold of JDBC or does it maintain a

statementPool

of it's own?

In my Log files of postgresql each query is called like this:

EXECUTE <unnamed> [PREPARE: select ...]

I have not found anything about preparing "unnamed" statements. What does

it

On Wed, 9 Jan 2008, mljv@planwerk6.de wrote:

- I know there is a PREPARE Statement in Postgresql and read the docs.
- in PostgresqlJDBC i have a prepareThreshold parameter which i left to
default of 5.
- in DBCP i have a property "poolPreparedStatements", set to true. Does ist
just configure prepareThreshold of JDBC or does it maintain a statementPool
of it's own?

The postgresql JDBC driver does not have a statement pool of its own, so
the two options prepareThreshold and poolPreparedStatements are
complementary. By itself the JDBC driver will switch to a long term
prepared plan once you've used the exact same PreparedStatement object
prepareThreshold number of times. Since it doesn't pool things behind the
scenes, you've got to retain a reference to the same PreparedStatement
object which is difficult or impossible in many applications. This is
where the DBCP statement pool comes in. It proxies PreparedStatement
wrappers to one underlying PG PreparedStatement object so that you can hit
prepareThreshold.

In my Log files of postgresql each query is called like this:

EXECUTE <unnamed> [PREPARE: select ...]

I have not found anything about preparing "unnamed" statements. What
does it mean?

Unnamed statements are what the driver uses before it hits the
prepareThreshold limit. Once it has determined the statement will be
reused many times it changes to a named statement that has a longer
lifespan.

Kris Jurka

first: thanks a lot for your answer. it already helped me a lot, but i still
have some questions:

Am Mittwoch, 9. Januar 2008 21:16 schrieb Kris Jurka:

On Wed, 9 Jan 2008, mljv@planwerk6.de wrote:

- I know there is a PREPARE Statement in Postgresql and read the docs.
- in PostgresqlJDBC i have a prepareThreshold parameter which i left to
default of 5.
- in DBCP i have a property "poolPreparedStatements", set to true. Does
ist just configure prepareThreshold of JDBC or does it maintain a
statementPool of it's own?

The postgresql JDBC driver does not have a statement pool of its own, so
the two options prepareThreshold and poolPreparedStatements are
complementary. By itself the JDBC driver will switch to a long term
prepared plan once you've used the exact same PreparedStatement object
prepareThreshold number of times. Since it doesn't pool things behind the
scenes, you've got to retain a reference to the same PreparedStatement
object which is difficult or impossible in many applications.

ah! So it doesn't help if it's the same statement, it has to be the same
object! So DBCP has a statement pool like a map, say
Map<String, PreparedStatement>
so it can fetch the reference to already existing prepared Statement by
looking at the statement itself, right?

But JDBC itself uses the "PREPARE" sql command, right?
So the statement is not really unnamed its name is "<unnamed>" like
prepare "<unnamed>" as select * from table;
execute "<unnamed>";

But i can't see any DEALLOCATE statements in my log file.

This is
where the DBCP statement pool comes in. It proxies PreparedStatement
wrappers to one underlying PG PreparedStatement object so that you can hit
prepareThreshold.

In my Log files of postgresql each query is called like this:

EXECUTE <unnamed> [PREPARE: select ...]

I have not found anything about preparing "unnamed" statements. What
does it mean?

Unnamed statements are what the driver uses before it hits the
prepareThreshold limit. Once it has determined the statement will be
reused many times it changes to a named statement that has a longer
lifespan.

What do you mean with "longer lifespan"? Doesn't the JDBC driver uses the
PREPARE Sql Statement and therefore the prepared Statement has the same
lifespan as the connection? If so, as connections are pooled and never
closed, the prepared Statement will last forever. What if the table analyzes
changes and a better execution plan could be found?

kind regards,
janning

On 1/10/08, mljv@planwerk6.de <mljv@planwerk6.de> wrote:

I have not found anything about preparing "unnamed" statements. What
does it mean?

Unnamed statements are what the driver uses before it hits the
prepareThreshold limit. Once it has determined the statement will be
reused many times it changes to a named statement that has a longer
lifespan.

What do you mean with "longer lifespan"? Doesn't the JDBC driver uses the
PREPARE Sql Statement and therefore the prepared Statement has the same
lifespan as the connection? If so, as connections are pooled and never
closed, the prepared Statement will last forever. What if the table analyzes
changes and a better execution plan could be found?

An unnamed prepared query is just a normal one-shot query, but it
allows the use of bind variables.

You'll find more details in the protocol section of the postgres docs
<http://www.postgresql.org/docs/8.3/static/protocol.html&gt;.

Regards,
Isak

On Thu, 10 Jan 2008, mljv@planwerk6.de wrote:

ah! So it doesn't help if it's the same statement, it has to be the same
object! So DBCP has a statement pool like a map, say
Map<String, PreparedStatement>
so it can fetch the reference to already existing prepared Statement by
looking at the statement itself, right?

Exactly.

But JDBC itself uses the "PREPARE" sql command, right?
So the statement is not really unnamed its name is "<unnamed>" like
prepare "<unnamed>" as select * from table;
execute "<unnamed>";

But i can't see any DEALLOCATE statements in my log file.

The JDBC driver does not use SQL level PREPARE / DEALLOCATE calls, but
instead uses protocol level commands that are pretty much equivalent.
The logging process tries to log sql and protocol level commands the same
way which is why it shows up that way in the log. Perhaps the server
should log the protocol level deallocate as well?

What do you mean with "longer lifespan"? Doesn't the JDBC driver uses the
PREPARE Sql Statement and therefore the prepared Statement has the same
lifespan as the connection? If so, as connections are pooled and never
closed, the prepared Statement will last forever. What if the table analyzes
changes and a better execution plan could be found?

Data and stats changes do not trigger a replan.

Kris Jurka

Kris Jurka <books@ejurka.com> writes:

On Thu, 10 Jan 2008, mljv@planwerk6.de wrote:

What do you mean with "longer lifespan"? Doesn't the JDBC driver uses the
PREPARE Sql Statement and therefore the prepared Statement has the same
lifespan as the connection? If so, as connections are pooled and never
closed, the prepared Statement will last forever. What if the table analyzes
changes and a better execution plan could be found?

Data and stats changes do not trigger a replan.

Note that this is no longer true as of 8.3: a stats update from ANALYZE
(either manual or autovacuum) will trigger invalidation of cached plans.

regards, tom lane

Am Sonntag 13 Januar 2008 00:46:50 schrieb Tom Lane:

Kris Jurka <books@ejurka.com> writes:

On Thu, 10 Jan 2008, mljv@planwerk6.de wrote:

What do you mean with "longer lifespan"? Doesn't the JDBC driver uses
the PREPARE Sql Statement and therefore the prepared Statement has the
same lifespan as the connection? If so, as connections are pooled and
never closed, the prepared Statement will last forever. What if the
table analyzes changes and a better execution plan could be found?

Data and stats changes do not trigger a replan.

Note that this is no longer true as of 8.3: a stats update from ANALYZE
(either manual or autovacuum) will trigger invalidation of cached plans.

great, i have too look out for 8.3, but i am quite happy with 8.1 because of
automatic security updates coming with debian out of the box. I have to look
for another way to replan. Maybe i just have to close and reopen my
connections from time to time.

kind regards
Janning