Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hrK7g-00086i-SX for pgsql-hackers@arkaria.postgresql.org; Sat, 27 Jul 2019 10:40:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hrK7d-0005Pe-Jj for pgsql-hackers@arkaria.postgresql.org; Sat, 27 Jul 2019 10:40:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hrK7d-0005Lt-9M for pgsql-hackers@lists.postgresql.org; Sat, 27 Jul 2019 10:40:17 +0000 Received: from mail-pl1-f181.google.com ([209.85.214.181]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hrK7a-0000gX-N3 for pgsql-hackers@postgresql.org; Sat, 27 Jul 2019 10:40:16 +0000 Received: by mail-pl1-f181.google.com with SMTP id i2so25710745plt.1 for ; Sat, 27 Jul 2019 03:40:14 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=6PRssZrem5nGxn1IaWeU9PT1hd6emrmzbUUSNs2Dgco=; b=A6drIaE6uyRnThcCbBJUTNYgNLcGQtv0tRivsFNEmS9shD8NqConQZD1tiNgAgBIqp tDTcwDjla3+PRNTzHcjSROhu25GnSsx6UKdFavn4bfg5KpRNHvsGp2WPbbMxIewV/sef niF1fspclSh2OtSqL8s8I619hAXiB+C+0RbjqiketVxdMwVtOZQylF9W6SX0IZGO0LXK 52iHjDu6NvHumFnRlSpcNvnrg0+ArQ+rUx1QwmFFYOeRdvgOzqj9vqS5ht5zxREKakbr +zLJ3YNJ7bFdJmSrcO5JuDHDhTnjzVwunwJ6I09lvR7Dju5Dt4g5IpY6xeyp4fYWwSYr 7RoA== X-Gm-Message-State: APjAAAWLv4V5E+U2921937UsquZkl5XPrQKPONE3fMKIf0ILKgx1uE7J TIVSkGOJusJDZAwm03Hmf//OihK9aLctlMpoEVY= X-Google-Smtp-Source: APXvYqw4/OejJgqJrryAHCBASEZUFhp02KdJ77n9/dwGaBXxqgiVId381IccALCPh741FGg5JZewZXMSap7+dxUGqLg= X-Received: by 2002:a17:902:740a:: with SMTP id g10mr101395032pll.82.1564224011787; Sat, 27 Jul 2019 03:40:11 -0700 (PDT) MIME-Version: 1.0 References: <139e59f5-15bc-13ff-58e2-7a2229041236@postgrespro.ru> <78d43611-668e-a4e2-4796-dbffccc2512d@postgrespro.ru> <42c78b0e-e2ad-7920-3b31-37a81c9ba83b@postgrespro.ru> <20190726202437.2svtvcisiujqaoky@development> In-Reply-To: <20190726202437.2svtvcisiujqaoky@development> From: Dave Cramer Date: Sat, 27 Jul 2019 06:40:00 -0400 Message-ID: Subject: Re: Built-in connection pooler To: Tomas Vondra Cc: Konstantin Knizhnik , Ryan Lambert , Thomas Munro , PostgreSQL Hackers , Bruce Momjian , Dimitri Fontaine Content-Type: multipart/alternative; boundary="00000000000092610f058ea748fe" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --00000000000092610f058ea748fe Content-Type: text/plain; charset="UTF-8" Responses inline. I just picked up this thread so please bear with me. On Fri, 26 Jul 2019 at 16:24, Tomas Vondra 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 > > --00000000000092610f058ea748fe Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Responses inline. I just picked up this thread so ple= ase bear with me.

On Fri, 26 Jul 2019 at 16:24, Tomas Vondra <tomas.vondra@2ndquadrant.com> w= rote:
Hi Konstan= tin,

I've started reviewing this patch and experimenting with it, so let me<= br> 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 o= f
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 effo= rt
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 t= ell from scanning this thread pooling would not be used at all.I suspect th= e .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.
=C2=A0
Agreed, and as for other projects. I see their value in having the pool = on a separate host as being a strength.=C2=A0 I certainly don't see the= m going anywhere soon. Either way having a unified pooling API would be a u= seful 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:

=C2=A0 BEGIN;
=C2=A0 SET LOCAL enable_nestloop =3D off;
=C2=A0 ...
=C2=A0 COMMIT;

or

=C2=A0 PREPARE x(int) AS SELECT ...;
=C2=A0 EXECUTE x(1);
=C2=A0 EXECUTE x(2);
=C2=A0 ...
=C2=A0 EXECUTE x(100000);
=C2=A0 DEALLOCATE x;
=C2=A0
Again pgjdbc doe= s use server prepared statements so I'm assuming this would not work fo= r clients using pgjdbc or .net=C2=A0

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

Regards,

Dave

--00000000000092610f058ea748fe--