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

Re: The logic behind the prepared statement in PostgreSQL

From: louis <louis(at)ibms(dot)sinica(dot)edu(dot)tw>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: The logic behind the prepared statement in PostgreSQL
Date: 2010-10-06 13:29:42
Message-ID: 1286371782.17773.51.camel@london (view raw or flat)
Thread:
Lists: pgsql-jdbc
On Thu, 2010-10-07 at 01:19 +1300, Oliver Jowett wrote:
> 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)

"JDBC code tracing" means firing up a debugger while running the code,
as you have expected. Sorry for the unclear expression.
Thanks for showing me the call stack.

However, while tracing the call stack I found that the
core.v3.QueryExecutorImpl.sendBind method sends a query statement Name
to PGStream, does it mean JDBC driver use the statement name to identify
the corresponding execution plan on a Postgresql server?
I have such assumption since PGStream seems to be the connection between
JDBC driver and the PostgreSQL server, and I can't find other obvious
path for JDBC code to specify an execution plan on the server side.

> 
> > 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
> 

Thanks for enlightening me on this issue. 
Your explanation is very helpful.

Cheers,


Louis



In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2010-10-06 21:16:06
Subject: Re: The logic behind the prepared statement in PostgreSQL
Previous:From: Oliver JowettDate: 2010-10-06 12:19:59
Subject: Re: The logic behind the prepared statement in PostgreSQL

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