From: Oliver Jowett <oliver@opencloud.com>
To: Scott Carey <scott@richrelevance.com>
Cc: Guillaume Smet <guillaume.smet@gmail.com>
Cc: Guillaume Cottenceau <gc@mnc.ch>
Cc: Mario Splivalo <mario.splivalo@megafon.hr>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>
Subject: Re: [PERFORM] Query much slower when run from postgres function
Date: Tue, 10 Mar 2009 10:40:26 +1300
Message-ID: <49B58CCA.8060308@opencloud.com> (raw)
In-Reply-To: <C5DAD06F.31CB%scott@richrelevance.com>
References: <C5DAD06F.31CB%scott@richrelevance.com>
Scott Carey wrote:
>
> 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’.
I've suggested that as a protocol-level addition in the past, but it
would mean a new protocol version. The named vs. unnamed statement
behaviour was an attempt to crowbar it into the protocol without
requiring a version change. If it's really a planner behaviour thing,
maybe it does belong at the SET level, but I believe that there's
usually an aversion to having to SET anything per query to get
reasonable plans.
> 2. How do you use those on a granularity other than global from jdbc?
prepareThreshold=N (as part of a connection URL),
org.postgresql.PGConnection.setPrepareThreshold() (connection-level
granularity), org.postgresql.PGStatement.setPrepareThreshold()
(statement-level granularity). See the driver docs.
> ( — I tried setting max_prepared_transactions to 0 but this
> didn’t seem to work either, and it would be global if it did).
max_prepared_transactions is to do with two-phase commit, not prepared
statements.
> 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.
It's unfortunate that ended up doing this, because it >is< all
configurable on the JDBC side. Did you ask on pgsql-jdbc?
-O
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: oliver@opencloud.com, scott@richrelevance.com, guillaume.smet@gmail.com, gc@mnc.ch, mario.splivalo@megafon.hr, pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Query much slower when run from postgres function
In-Reply-To: <49B58CCA.8060308@opencloud.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox