Re: [PERFORM] Query much slower when run from postgres function

1. And how do you do that from JDBC? There is no standard concept of 'unnamed' prepared statements in most database APIs, and if there were the behavior would be db specific. Telling PG to plan after binding should be more flexible than unnamed prepared statements - or at least more transparent to standard APIs. E.g. SET plan_prepared_postbind='true'.
2. How do you use those on a granularity other than global from jdbc? ( - I tried setting max_prepared_transactions to 0 but this didn't seem to work either, and it would be global if it did). Prepared statements are still great for things like selecting off a primary key, or especially inserts. Controls at the connection or user level would be significantly more valuable than global ones.
3. Is it possible to test them from psql? (documentation is weak, PREPARE requires a name, functions require names, etc .. C api has docs but that's not of use for most).

I'd love to know if there were answers to the above that were workable.

In the end, we had to write our own client side code to deal with sql injection safely and avoid jdbc prepared statements to get acceptable performance in many cases (all cases involving partitioned tables, a few others). At least dollar-quotes are powerful and useful for dealing with this. Since the most important benefit of prepared statements is code clarity and sql injection protection, its sad to see weakness in control/configuration over prepared statement behavior at the parse/plan level get in the way of using them for those benefits.

On 3/9/09 9:04 AM, "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com> wrote:

On Mon, Mar 9, 2009 at 5:51 PM, Guillaume Cottenceau <gc(at)mnc(dot)ch> wrote:
> Until it's possible to specifically tell the JDBC driver (and/or
> PG?) to not plan once for all runs (or is there something better
> to think of?), or the whole thing would be more clever (off the
> top of my head, PG could try to replan with the first actual
> values - or first xx actual values - and if the plan is
> different, then flag that prepared statement for replanning each
> time if the overall time estimate is different enough), I've
> opted to tell the JDBC driver to use the protocol version 2, as
> prepared statements were not so much prepared back then (IIRC
> parameter interpolation is performed in driver and the whole SQL
> query is passed each time, parsed, and planned) using
> protocolVersion=2 in the JDBC URL. So far it worked very well for
> us.

Unnamed prepared statements are planned after binding the values,
starting with 8.3, or more precisely starting with 8.3.2 as early 8.3
versions were partially broken on this behalf.

It's not always possible to use protocol version 2 as it's quite
limited (especially considering the exceptions returned).

--
Guillaume

--
Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance