Re: Built-in connection pooler

On 06.09.2019 1:01, Jaime Casanova wrote:
> On Thu, 15 Aug 2019 at 06:01, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> I have implemented one more trick reducing number of tainted backends:
>> now it is possible to use session variables in pooled backends.
>>
>> How it works?
>> Proxy determines "SET var=" statements and converts them to "SET LOCAL
>> var=".
>> Also all such assignments are concatenated and stored in session context
>> at proxy.
>> Then proxy injects this statement inside each transaction block or
>> prepend to standalone statements.
>>
>> This mechanism works only for GUCs set outside transaction.
>> By default it is switched off. To enable it you should switch on
>> "proxying_gucs" parameter.
>>
>>
> there is definitively something odd here. i applied the patch and
> changed these parameters
>
> connection_proxies = '3'
> session_pool_size = '33'
> port = '5433'
> proxy_port = '5432'
>
> after this i run "make installcheck", the idea is to prove if an
> application going through proxy will behave sanely. As far as i
> understood in case the backend needs session mode it will taint the
> backend otherwise it will act as transaction mode.
>
> Sadly i got a lot of FAILED tests, i'm attaching the diffs on
> regression with installcheck and installcheck-parallel.
> btw, after make installcheck-parallel i wanted to do a new test but
> wasn't able to drop regression database because there is still a
> subscription, so i tried to drop it and got a core file (i was
> connected trough the pool_worker), i'm attaching the backtrace of the
> crash too.
>

Thank you very much for testing connection pooler.
The problem with "make installcheck" is caused by GUCs passed by
pg_regress inside startup packet:

    putenv("PGTZ=PST8PDT");
    putenv("PGDATESTYLE=Postgres, MDY");

Them are not currently handled by builtin proxy.
Just because I didn't find some acceptable solution for it.
With newly added proxying_gucs options it is possible, this problem is
solved, but it leads to other problem:
some Postgres statements are not transactional and can not be used
inside block.
As far as proxying_gucs appends gucs setting to the statement (and so
implicitly forms transaction block),
such statement cause errors. I added check to avoid prepending GUCs
settings to non-transactional statements.
But this check seems to be not so trivial. At least I failed to make it
work: it doesn;t correctly handle specifying default namespace.

"make installcheck" can be passed if you add the folowing three settings
to configuration file:

datestyle='Postgres, MDY'
timezone='PST8PDT'
intervalstyle='postgres_verbose'

Sorry, I failed to reproduce the crash.
So if you will be able to find out some scenario for reproduce it, I
will be very pleased to receive it.

I attached to this main new version of the patch. It includes
multitenancy support.
Before separate proxy instance is created for each <dbname,role> pair.
Postgres backend is not able to work with more than one database.
But it is possible to change current user (role) inside one connection.
If "multitenent_proxy" options is switched on, then separate proxy will
be create only for each database and current user is explicitly
specified for each transaction/standalone
statement using "set command" clause.
To support this mode you need to grant permissions to all roles to
switch between each other.

So basically multitenancy support uses the same mechanism as GUCs proxying.
I will continue work on improving GUCs proxying mechanism, so that it
can pass regression tests.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company