Re: Global Named Prepared Statements

On Mon, May 21, 2012 at 8:55 AM, Samba <saasira(at)gmail(dot)com> wrote:
> If Stored Procedures are equivalent to prepared statements [ as far as
> preparing the query plan is concerned], then what i'm looking for is perhaps
> a Global Prepared Statements at the client/driver side.
>
> Specifically, It wold be good if the JDBC driver prepares all the queries
> for invoking stored procedures at once per JVM so that each connection need
> not incur the cost of preparing [parsing and storing] those queries per
> connection.
>
> Thus we can put all the queries [stored procedure calls] at a single place,
> and prepare those queries during boot of the server [or deployment of the
> web application], and then execute those queries endless times by closing
> just the resultset object while keeping the statement open for ever.
>
> I know this is not form to discuss the JDBC related questions but put my
> thoughts here to complete the question i raised. If folks think this idea is
> valid then i will take it up with the JDBC Driver team.

Well, there is a client side component to statement preparation which
the JDBC driver also does.

There is a reason why there are no global plans in postgres: it
complicates everything in the sense that there you have to deal with
shared memory, locking. and scope/lifetime issues. Even though it can
be a big reduction in memory consumption you're on the wrong side of
the tradeoff for most cases. If you want to leverage server side
objects with >1 client connections I strongly advise looking at a
connection pooler -- not the lame client side pooling solutions you
typically see with the java stack -- but something like pgbouncer.
This amortizes memory costs of server side plans. pgbouncer is
mostly compatible with JDBC; you have to disable automatic statement
preparation.

merlin