Re: The logic behind the prepared statement in PostgreSQL

louis wrote:

> 1. Even though all the prepare statements have the identical SQL string, the postgresql server creates one execution plan for each statement.
> So I think the execution plan is fetched by name (S_1, S_2, etc.) instead of SQL query string from the server-side. On the other hand,
> a JDBC code tracing shows that the execution plan is fetched by SQL query String. Is the above assertion correct?

From the driver's point of view, if you create multiple different
PreparedStatement objects (as you are doing here), each is a completely
separate statement - even if they happen to share a query string with
some other statement you executed. So a separate server-side statement
is prepared for each.

If you reuse the PreparedStatement object, the driver will reuse the
underlying server-side statement it prepared earlier.

I don't know what you mean by "JDBC code tracing" exactly, but there's
no mapping of SQL query string to statement name; there is just an
association from a PreparedStatement to a corresponding server-side
statement (see e.g. jdbc2.AbstractJdbc2Statement.preparedQuery ->
core.Query -> core.v3.SimpleQuery)

> 3. In the 5th iteration all the previously-prepared statements are dropped from the postgresql server, the pg_prepared_statements shows the following:
>
> pg_prepared_statemnt S_6 select * from gsd_36c_unique where tag_length = $1 2010-10-06 19:01:06.99907+08 {integer} f
> pg_prepared_statemnt S_1 BEGIN 2010-10-06 19:01:06.052524+08 {} f
>
> I think it's because postgresql JDBC Driver has a default limit for the max number of preparedStatement, which is 4.
> Can anyone tell me where to adjuest this parameter?

This is just luck of the draw. It is because you are leaking the
statements, rather than closing them. There is a reference queue +
phantom reference that is used to detect this case and free any
server-side resources associated with leaked statements, but exactly
when those references are cleared and enqueued is entirely up to the
JVM's garbage collector. The driver polls the queue before each query
execution and sends appropriate protocol messages to free the
server-side statements of any enqueued references.

If you properly close the prepared statement, the reference is
immediately cleared/enqueued, so you don't have to wait for GC in that case.

Oliver