Re: Built-in connection pooler

Responses inline. I just picked up this thread so please bear with me.

On Fri, 26 Jul 2019 at 16:24, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

> Hi Konstantin,
>
> I've started reviewing this patch and experimenting with it, so let me
> share some initial thoughts.
>
>
> 1) not handling session state (yet)
>
> I understand handling session state would mean additional complexity, so
> I'm OK with not having it in v1. That being said, I think this is the
> primary issue with connection pooling on PostgreSQL - configuring and
> running a separate pool is not free, of course, but when people complain
> to us it's when they can't actually use a connection pool because of
> this limitation.
>
> So what are your plans regarding this feature? I think you mentioned
> you already have the code in another product. Do you plan to submit it
> in the pg13 cycle, or what's the plan? I'm willing to put some effort
> into reviewing and testing that.
>

I too would like to see the plan of how to make this feature complete.

My concern here is that for the pgjdbc client at least *every* connection
does some set parameter so I see from what I can tell from scanning this
thread pooling would not be used at all.I suspect the .net driver does the
same thing.

> FWIW it'd be nice to expose it as some sort of interface, so that other
> connection pools can leverage it too. There are use cases that don't
> work with a built-in connection pool (say, PAUSE/RESUME in pgbouncer
> allows restarting the database) so projects like pgbouncer or odyssey
> are unlikely to disappear anytime soon.
>

Agreed, and as for other projects. I see their value in having the pool on
a separate host as being a strength. I certainly don't see them going
anywhere soon. Either way having a unified pooling API would be a useful
goal.

> I also wonder if we could make it more permissive even in v1, without
> implementing dump/restore of session state.
>
> Consider for example patterns like this:
>
> BEGIN;
> SET LOCAL enable_nestloop = off;
> ...
> COMMIT;
>
> or
>
> PREPARE x(int) AS SELECT ...;
> EXECUTE x(1);
> EXECUTE x(2);
> ...
> EXECUTE x(100000);
> DEALLOCATE x;
>

Again pgjdbc does use server prepared statements so I'm assuming this would
not work for clients using pgjdbc or .net

Additionally we have setSchema, which is really set search_path, again
incompatible.

Regards,

Dave

>
>