Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qUOEU-00Ciza-VN for pgadmin-hackers@arkaria.postgresql.org; Fri, 11 Aug 2023 09:14:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qUOET-005zp9-DI for pgadmin-hackers@arkaria.postgresql.org; Fri, 11 Aug 2023 09:14:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qUOET-005zp1-1o for pgadmin-hackers@lists.postgresql.org; Fri, 11 Aug 2023 09:14:57 +0000 Received: from mail-yw1-x112d.google.com ([2607:f8b0:4864:20::112d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qUOEP-001lrl-UE for pgadmin-hackers@postgresql.org; Fri, 11 Aug 2023 09:14:55 +0000 Received: by mail-yw1-x112d.google.com with SMTP id 00721157ae682-5768a7e3adbso41460877b3.0 for ; Fri, 11 Aug 2023 02:14:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1691745292; x=1692350092; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=/jIzo6sM0c1qwuueeU6/dEduqQIVzetNLy05i/UTZJA=; b=CAWYojQ5YNVQOYezoxJlYBMj0sdY6hmVF3bisZ0pfh+euCsBhvKw4AX/kgNvxHU5Oz sq+G0IwtduRz9NhJP405ickrkJobjdfUcq/qqWqTTWD4owmj77mIxnG7i/PvXndiq1c/ j/LHTVwwvu1NbTOAKHmqJC47dD69InPfS9k+xTN8v17SRojfVLigi4UQTVIUG6JLeqZp g3mTCzE1Hmz2XPNMVunquL/lmZwEY1IAsxuts68ZUzUYQQeKZSuB4Lj1m6uhEILHp6Rg wbdkgtqWnyhAh+4UBvWkgSMqq6sj1VfVzeFewJPBFgpL/LNUz4z0XKufiu2BuagRlEFC OBoA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691745292; x=1692350092; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=/jIzo6sM0c1qwuueeU6/dEduqQIVzetNLy05i/UTZJA=; b=RJl/9cmCtj2tq+wyiOaNTcE8Lj6LHkv+JHl6NtixpiGCo+BPIhthXbGxxBqcz8HfLo 56J3ZL8TTTxx8aM5DAenVxQlT8nExzhUVT7GOn2M4cHD/zMSYvOWZX/8z9fyKUIGH8PJ cY4OO2JicfibQ61yfUZdwUvaydTyVOGWn5ZxFQTnqdcaQULXxVMpbfSOz3E3UZ0zMIyw BcQZ2uCQ81LVbcOzjlfktZWQPsB0MGwcYqMPcQLt1QrQ+oIhufEaJ8mvg0AwJlLV2jTH 39jlfkU+Di8USbGhx+SsDSUV/M4ZzVSqbNTXZ0B+69Zp88hifDuG+Wj8E1NUXJ5qYhK/ nlhg== X-Gm-Message-State: AOJu0YwOyiv2S32z/3daYLfkxEuEVDL7GI8owACICpLUIVFRyY+VezF4 WwmeV040zGbgANUtiZCE0Jeuw++t+HRCgL/ZMspEBg== X-Google-Smtp-Source: AGHT+IHxp/q2Euk9K2N4+M1nVjzRH+gbPz+bnEAgyHgw1RjX+yDXBH12Dn4IzRWs6MsccznVdnsXWH9ypPFrS5QUh+4= X-Received: by 2002:a81:5292:0:b0:56c:e5a3:3e09 with SMTP id g140-20020a815292000000b0056ce5a33e09mr5517302ywb.15.1691745292493; Fri, 11 Aug 2023 02:14:52 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Yogesh Mahajan Date: Fri, 11 Aug 2023 14:44:16 +0530 Message-ID: Subject: Re: Request for inputs on #6208 - MaxQueuePool Issue To: Dave Page Cc: Akshay Joshi , pgadmin-hackers , Nikhil Mohite Content-Type: multipart/alternative; boundary="00000000000035c2170602a2250f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000035c2170602a2250f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Dave On Fri, Aug 11, 2023 at 2:40=E2=80=AFPM Dave Page wrote= : > > > On Wed, 9 Aug 2023 at 08:25, Akshay Joshi > wrote: > >> Hi Yogesh >> >> On Tue, Aug 8, 2023 at 1:48=E2=80=AFPM Yogesh Mahajan < >> yogesh.mahajan@enterprisedb.com> wrote: >> >>> Hi Hackers, >>> >>> For the #6208 , be= low >>> are the observations about this issue - >>> >>> On each API request, an application db connection is created with state >>> 'idle in transaction'. Connection state is changed to idle only after a >>> successful response from the web server. If an exception occurs while >>> processing a request which is not handled and response is not sent, the >>> application db connection remains orphaned. This connection is only res= et >>> on application restart. >>> >>> Issue - In case of pgAdmin, if the user openes 15 query tool tabs & all >>> of them have long running transactions like pg_sleep(), then opening ne= w >>> query tool/or any operation on pgAdmin which hits API request to backen= d >>> will throw 'QueuePool Limit reached' error. (Because by default, SQLAlc= hemy >>> allows 15 connections total: 5 connections in pool & 10 in overflow and >>> pgAdmin uses default setting.) OR if the user executes a query in the >>> query tool & while query execution is in progress, hits F5(keyboard >>> shortcut to execute query) 16 times continuously, then pgAdmin throws >>> 'QueuePool Limit reached' error. After that, the query tool gives incor= rect >>> responses to the queries. >>> >>> Solutions - >>> 1.Provide configurable settings for 'pool_size' & 'max_overflow' >>> parameters for SQLAlchemy. >>> 2.Disable pooling using NullPool.A Pool which does not pool connections= . >>> Instead it literally opens and closes the underlying DB-API connection = per >>> each connection open/close. Using NullPool may impact the performance. >>> >>> What approach should be followed to fix the issue? >>> >> >> If NullPool *may* impact the performance then we should go with >> Solution 1. >> > > Yes, with a much larger default value I would suggest. > Okay.Will proceed with solution 1. > > Plus, we should also stop leaving orphaned connections behind... > Fix for this is already commited. > > > -- > Dave Page > Blog: https://pgsnake.blogspot.com > Twitter: @pgsnake > > EDB: https://www.enterprisedb.com > > Thanks, Yogesh Mahajan EnterpriseDB --00000000000035c2170602a2250f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Dave

On Fri, Aug 11, 2= 023 at 2:40=E2=80=AFPM Dave Page <d= page@pgadmin.org> wrote:
=


On Wed, 9 Aug 2023 at 08:25, Akshay Joshi <akshay.joshi@en= terprisedb.com> wrote:
Hi=C2=A0Yogesh

On Tue, Aug 8, 2023 at 1:48=E2=80=AFPM Yogesh= Mahajan <yogesh.mahajan@enterprisedb.com> wrote:
Hi Hackers,
For the=C2=A0#6208= , below are the observations about this issue=C2=A0-=C2=A0=

On each API reques= t, an application db connection is created with state 'idle in transact= ion'. Connection state is changed to idle only after a successful respo= nse from the web server. If an exception occurs while processing a request = which is not handled and response is not sent, the application db connectio= n remains orphaned. This connection is only reset on application restart.

Issue -=C2=A0In case of pgAdmin,=C2=A0if the user openes 15 query too= l tabs & all of them have long running transactions like pg_sleep(), th= en opening new query tool/or any operation on pgAdmin which hits API reques= t to backend will throw 'QueuePool Limit reached' error. (Because=C2=A0by default,=C2=A0SQLAlchemy allows 15 connections total: 5 connections in po= ol & 10 in overflow and pgAdmin uses default setting.) OR =C2=A0if the user executes a qu= ery in the query tool & while query execution is in progress, hits F5(k= eyboard shortcut to execute query) 16 times continuously, then pgAdmin thro= ws 'QueuePool Limit reached' error. After that, the query tool give= s incorrect responses to the queries.
Solutions=C2=A0-=C2=A0
1.Provide configurable settings for 'p= ool_size' & 'max_overflow' parameters for SQLAlchemy.
2.Disable pooling using NullPool.A Pool which does not po= ol connections. Instead it literally opens and closes the underlying DB-API= connection per each connection open/close. Using NullPool may impact th= e performance.=C2=A0

What approach should= be followed to fix=C2=A0the issue?

=C2=A0 =C2=A0 If NullPool may impact the perfor= mance then we should go with Solution 1.=C2=A0

Yes, with a much larger default value I would suggest= .
=C2=A0
Okay.Will proce= ed with solution 1.

Plus, we should also stop leaving orphaned c= onnections behind...=C2=A0

Fix for this is already commited.


--


Tha= nks,
Yogesh Mahajan
EnterpriseDB= =C2=A0
--00000000000035c2170602a2250f--