Global Named Prepared Statements
Hi,
Does postgresql support Global Prepared Statements, which are prepared only
once per server and not per every connection?
I see a discussion about this in the pgsql-hacker archives but it does not
have any conclusion; further, that discussion also deviates a bit from my
question by proposing to cache any arbitrary statements when used too often
based on statistical analyses.
here is the original thread :
http://archives.postgresql.org/pgsql-hackers/2008-03/msg01228.php
I noticed that a similar feature request is made in mysql community as
well; link here: http://bugs.mysql.com/bug.php?id=38732
Problem with per-connection prepared statements is that the onus of
preparing those statements for each connection lies with the client which
makes those connections. Ideally, the performance of an application must be
decided by the Server that hosts and not by the client that uses the
service.
Hence, it would be great if from a connection C1 i can prepare the
statement:
PREPARE GLOBAL fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
And From Connections C2-Cn, I can execute the same statement with bind
parameters:
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
This would help DBA to define the important and costly but fine-tuned
queries and expose only the prepared statement names to the application
developers. This will avoid scenarios like the developers forgetting to
prepare all the required statements per each connection on the one hand and
to make sure that they do not try to prepare it again and again which would
be causing errors and if not handled properly may cause functionality to be
broken at unexpected places.
Rather, if one can prepare named statements globally at once and then reuse
them through the entire uptime of the server, would that not be a lot more
beneficial?
If it is observed that a particular prepared statement is not behaving
properly, then it can be deallocated and fixed and then prepared again.
I'm not that much sure whether such a feature is already implemented in
postgres or not hence posting it to general mailing list; if folks feel
that it ought to go to hackers list, then please guide me so.
Thanks and Regards,
Samba
Samba <saasira@gmail.com> writes:
Does postgresql support Global Prepared Statements, which are prepared only
once per server and not per every connection?
No.
regards, tom lane
On Tue, May 15, 2012 at 05:38:27AM +0530, Samba wrote:
Hi,
Does postgresql support Global Prepared Statements, which are prepared only
once per server and not per every connection?
As pointed out, no.
Problem with per-connection prepared statements is that the onus of
preparing those statements for each connection lies with the client which
makes those connections. Ideally, the performance of an application must be
decided by the Server that hosts and not by the client that uses the
service.
How is this different from using CREATE FUNCTION to create a function
which has the desired effect? This is a well understood and commonly
used paradigm. When using a connection pooler any query plan caching
will happen automatically.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
He who writes carelessly confesses thereby at the very outset that he does
not attach much importance to his own thoughts.
-- Arthur Schopenhauer
On Tue, May 15, 2012 at 1:21 AM, Martijn van Oosterhout
<kleptog@svana.org> wrote:
On Tue, May 15, 2012 at 05:38:27AM +0530, Samba wrote:
Hi,
Does postgresql support Global Prepared Statements, which are prepared only
once per server and not per every connection?As pointed out, no.
Problem with per-connection prepared statements is that the onus of
preparing those statements for each connection lies with the client which
makes those connections. Ideally, the performance of an application must be
decided by the Server that hosts and not by the client that uses the
service.How is this different from using CREATE FUNCTION to create a function
which has the desired effect? This is a well understood and commonly
used paradigm. When using a connection pooler any query plan caching
will happen automatically.
this is not necessarily true, right? for example, 'sql' language
functions don't cache plans while plpgsql functions mostly (no
EXECUTE) do. other languages will typically have ability to save
plans (or not). but yeah, functions generally cover this case quite
nicely.
i rarely use prepared statements anymore but if you're counting
microseconds of latency for trivial queries, they still have a niche
role...but to really see the benefit you'd want to be coding directly
against the C api and making the appropriate calls (PQexecPrepared,
etc).
merlin
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.
Thanks and Regards,
Samba
=====================================================
On Tue, May 15, 2012 at 6:46 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
Show quoted text
On Tue, May 15, 2012 at 1:21 AM, Martijn van Oosterhout
<kleptog@svana.org> wrote:On Tue, May 15, 2012 at 05:38:27AM +0530, Samba wrote:
Hi,
Does postgresql support Global Prepared Statements, which are prepared
only
once per server and not per every connection?
As pointed out, no.
Problem with per-connection prepared statements is that the onus of
preparing those statements for each connection lies with the clientwhich
makes those connections. Ideally, the performance of an application
must be
decided by the Server that hosts and not by the client that uses the
service.How is this different from using CREATE FUNCTION to create a function
which has the desired effect? This is a well understood and commonly
used paradigm. When using a connection pooler any query plan caching
will happen automatically.this is not necessarily true, right? for example, 'sql' language
functions don't cache plans while plpgsql functions mostly (no
EXECUTE) do. other languages will typically have ability to save
plans (or not). but yeah, functions generally cover this case quite
nicely.i rarely use prepared statements anymore but if you're counting
microseconds of latency for trivial queries, they still have a niche
role...but to really see the benefit you'd want to be coding directly
against the C api and making the appropriate calls (PQexecPrepared,
etc).merlin
On Mon, May 21, 2012 at 8:55 AM, Samba <saasira@gmail.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
Le lundi 21 mai 2012 16:08:27, Merlin Moncure a écrit :
On Mon, May 21, 2012 at 8:55 AM, Samba <saasira@gmail.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