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 1qTdYz-00AC4N-84 for pgadmin-hackers@arkaria.postgresql.org; Wed, 09 Aug 2023 07:25:01 +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 1qTdYx-0037jK-G6 for pgadmin-hackers@arkaria.postgresql.org; Wed, 09 Aug 2023 07:24:59 +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 1qTdYx-0037jB-3t for pgadmin-hackers@lists.postgresql.org; Wed, 09 Aug 2023 07:24:59 +0000 Received: from mail-lf1-x134.google.com ([2a00:1450:4864:20::134]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qTdYt-001LmC-Vs for pgadmin-hackers@postgresql.org; Wed, 09 Aug 2023 07:24:57 +0000 Received: by mail-lf1-x134.google.com with SMTP id 2adb3069b0e04-4fe85fd3d27so83966e87.0 for ; Wed, 09 Aug 2023 00:24:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1691565894; x=1692170694; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=NwplhmvribGfYN4WFhWkEFo4eUH3Zkc/O8trCU1k520=; b=dt5KnZIMQwz9Qu9q8UTWqcPg0YwPKV8Do4QysDKc7Z0jkN0Ct9dIl8azW0jF52NaKW Q40bCvxjbthYq+B8r6jWQB/Jwn6t1SFfEuhsT7xqrg5yZMlDzyn1SPy/eXGFoKICY9Ru W67qFRfH/SP+nJKGoVDUi8HbmLtefLHIQKCPune8v4eXkCbfhQyP5eP0tDtcxQizMxWT PoyXsgmJUZR/icuN47wkk5rbG28PAbvRSc8W/Ne//yjSfScX23Cf0aU8i4psb19YDc1w HLWFfhPsClT+zRLqT8zivsDXR4HHFle07wwosEhowh2RJtElSyu91lqBF9QfVWHAXxUI MM3w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691565894; x=1692170694; 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=NwplhmvribGfYN4WFhWkEFo4eUH3Zkc/O8trCU1k520=; b=MMsCdU/CaMIwYuPy6Wx7zsufgCVTA3UMTtQXT2+gdJTTDiV7ZBX3DW1BDZfSpyflWb yVKhASrEZm+UcMCcvB/lKvf80uYwEGHre4xdlzqs8pBSfYCiggHw+hh5IrnydxO7HXdE 9Axl+PwkdZSow37/I/4b6CfFdbN9E4l6gSHVSuyYEfgLzoMRDwYRJP+2vX2PHOO8+DiW ox25dChFZsDzlqzijJTW+TKS6mXg43mkF6jXjWyvzIPG+Vj/50tDFFIY7KU8JsptaVzn +FyJ/x7w1c1BEoKJWcvSGaf4r6UW0l14+4JuPVCcNISroNYlSYKm+tNCwjlCMiYWE5iz 3fvg== X-Gm-Message-State: AOJu0YzWkeSH9r+lUcAiMBj2Uzmsu0lecF4ousUw7NMD9HqMQjaEIuO1 fbivr8/jTkw11zzbJcgtlV1HIGH/1aj+TefWyBHr6A== X-Google-Smtp-Source: AGHT+IGIqSa5xFLwVWfenwAu7wut3Mz33JXSRFmjYYM2IpL8VCnAeZoFOs/43Gey75+WWwgDPzYcB/RSUieZhRwbkR4= X-Received: by 2002:a19:710b:0:b0:4fc:3756:754e with SMTP id m11-20020a19710b000000b004fc3756754emr1083430lfc.56.1691565894020; Wed, 09 Aug 2023 00:24:54 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Akshay Joshi Date: Wed, 9 Aug 2023 12:54:42 +0530 Message-ID: Subject: Re: Request for inputs on #6208 - MaxQueuePool Issue To: Yogesh Mahajan Cc: pgadmin-hackers , Nikhil Mohite Content-Type: multipart/alternative; boundary="0000000000003a56780602786009" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003a56780602786009 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 , belo= w > 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 reset > 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 new > query tool/or any operation on pgAdmin which hits API request to backend > will throw 'QueuePool Limit reached' error. (Because by default, SQLAlche= my > 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 incorre= ct > 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 pe= r > 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. > > > @Nikhil Mohite Please add if anything is > missed. > > Thanks, > Yogesh Mahajan > EnterpriseDB > --0000000000003a56780602786009 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 is= sue=C2=A0-=C2=A0

On each API request, an application db connection is creat= ed 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 o= nly reset on application restart.

Issue -=C2=A0In case of pgAdmin,=C2=A0<= /span>if the user openes 15 query tool tabs & all of them have long runni= ng transactions like pg_sleep(), then opening new query tool/or any operati= on on pgAdmin which hits API request to backend will throw 'QueuePool L= imit reached' error. (Because=C2=A0by default,=C2=A0SQLAlchemy allows 15 con= nections total: 5 connections in pool & 10 in overflow and pgAdmin uses= default setting.) OR =C2=A0if the user executes a query in the query tool & while query = execution is in progress, hits F5(keyboard shortcut to execute query) 16 ti= mes continuously, then pgAdmin throws 'QueuePool Limit reached' err= or. After that, the query tool gives incorrect responses to the queries.

Solutions=C2= =A0-=C2=A0
1.Prov= ide configurable settings for 'pool_size' & 'max_overflow&#= 39; parameters for SQLAlchemy.
2.Disable pooling usi= ng NullPool.A Pool which does not pool connections. Instead it literally op= ens and closes the underlying DB-API connection per each connection open/cl= ose. Using NullPool may impact the performance.=C2=A0
What approach should be followed to fix=C2=A0the issue?<= /span>

=C2=A0 =C2=A0 If N= ullPool may impact the performance then we should go with Solution 1= .=C2=A0

<= a class=3D"gmail_plusreply" id=3D"m_80757371465734232plusReplyChip-0" href= =3D"mailto:nikhil.mohite@enterprisedb.com" target=3D"_blank">@Nikhil Mohite= =C2=A0Please add if anything is missed.

Thanks,
Yogesh Mahajan
EnterpriseDB
--0000000000003a56780602786009--