Re: Prepared Statements

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(at)planwerk6(dot)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