Re: Global Named Prepared Statements

Le lundi 21 mai 2012 16:08:27, Merlin Moncure a écrit :
> 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.

and there is preprepare to help with prepared_statement and pgbouncer:

https://github.com/dimitri/preprepare

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation