On Thu, Apr 26, 2018 at 6:04 AM, Konstantin Knizhnik
<k.knizh...@postgrespro.ru> wrote:
> On 25.04.2018 20:02, Merlin Moncure wrote:
>> Yep. The main workaround today is to disable them. Having said that,
>> it's not that difficult to imagine hooking prepared statement creation
>> to a backend starting up (feature: run X,Y,Z SQL before running user
>> queries).
>
> Sorry, I do not completely understand your idea.
> Yes, it is somehow possible to simulate session semantic by prepending all
> session specific commands (mostly setting GUCs) to each SQL statements.
> But it doesn't work for prepared statements: the idea of prepared statements
> is that compilation of statement should be done only once.
The idea is that you have arbitrary SQL that runs when after the
backend (postgres binary) is forked from postmaster. This would be an
ideal place to introduce prepared statements in a way that is pooling
compatible; you still couldn't PREPARE from the application but you'd
be free to call already prepared statements (via SQL level EXECUTE or
libpq PQexecPrepared()). Of course, if somebody throws a DEALLOCATE
or DISCARD ALL, or issues a problematic DROP x CASCADE, you'd be in
trouble but that'a not a big deal IMO because you can control for
those things in the application.
> Database performance is mostly limited by disk, so optimal number of
> backends may be different from number of cores.
> But certainly possibility to launch "optimal" number of backends is one of
> the advantages of builtin session pooling.
Sure, but some workloads are cpu limited (all- or mostly- read with
data < memory, or very complex queries on smaller datasets). So we
would measure configure based one expectations exactly as is done
today with pgbouncer. This is a major feature of pgbouncer: being
able to _reduce_ the number of session states relative to the number
of connections is an important feature; it isolates your database from
various unpleasant failure modes such as runaway memory consumption.
Anyways, I'm looking at your patch. I see you've separated the client
connection count ('sessions') from the server backend instances
('backends') in the GUC. Questions:
*) Should non pooled connections be supported simultaneously with
pooled connections?
*) Should there be multiple pools with independent configurations (yes, please)?
*) How are you pinning client connections to an application managed
transaction? (IMNSHO, this feature is useless without being able to do
that)
FYI, it's pretty clear you've got a long road building consensus and
hammering out a reasonable patch through the community here. Don't
get discouraged -- there is value here, but it's going to take some
work.
merlin
