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 1qTHug-0093p0-Pa for pgadmin-hackers@arkaria.postgresql.org; Tue, 08 Aug 2023 08:17: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 1qTHuf-00FBtO-98 for pgadmin-hackers@arkaria.postgresql.org; Tue, 08 Aug 2023 08:17:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTHuf-00FBtG-1W for pgadmin-hackers@lists.postgresql.org; Tue, 08 Aug 2023 08:17:57 +0000 Received: from mail-yw1-x112b.google.com ([2607:f8b0:4864:20::112b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qTHuc-001Fgf-B1 for pgadmin-hackers@postgresql.org; Tue, 08 Aug 2023 08:17:56 +0000 Received: by mail-yw1-x112b.google.com with SMTP id 00721157ae682-583fe10bb3cso61450407b3.2 for ; Tue, 08 Aug 2023 01:17:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1691482672; x=1692087472; h=cc:to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=WDjCZa7lxjewhej+YQnSeIL0cLVlEr5aJt11lW8gZrw=; b=k7ERIHAoYaoZ2gwbLVsNBG+k/+JkbAJjxMwRAc4IOJGJGKaJlMTFtWVE12IgruMOFS pAQT0b61rUYj6/3ihhMV9k0N/pY2FzoUQgEzB9wOWZc9mJ5OO8MGU8g2xWX61tsvIzSs 8cB9dv1wW1jU0GxGu3YtNkH13EmPsM5SOAdDvE7ijqUX8mE+HxmmVmrzGiDyep5zpZGK RVsuIrDfUPAvutCLmwf7zcrewciFBHsQjuZGlYg0keC+hLg6c9IAwT/nicLQnfvI6NKX ZVDl3g2ynwcpjLKBpFoNeRPJ0R7ZkmP3je+09t8/kkgv6W6DzxqaaHgj7S0A6xrTdtpU /uYg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691482672; x=1692087472; h=cc:to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=WDjCZa7lxjewhej+YQnSeIL0cLVlEr5aJt11lW8gZrw=; b=Z7Nig8ONeLa2xDdru49kf96wif0PrlOIsjHM80teqp7FTcja0sMVrGUG995egd06eS EK2G0UkOJK5OmwGzvrYOoP0ISfdB1vH+2hvgU5eTtvEZZVQkgvuheN8+AJxeYVl+XPjt 7DjSF/Bk41netiZ9Ho1PETtGSeaBQuXNvOXorVZrFnrEScetVKmlVXcAJa06pkM9/axB Dd89/eeUsYih/oNUuVYPujYiB+NRInm4YFy6078BMx6tjACtwi27wrNaDliNmumXyeOy odrISGxHXJgS3HMGgp0dScOrJv85J+R0VWPAGQxUM1qay7ylWVWMk4fxHLCeKMGPrk6B zRZw== X-Gm-Message-State: AOJu0YyjGpy1YP75UdYFh+nNEmMPDwmeeTMHZbIgh3et23lVslfrGFAs vvfxzd+oQpKfUcAbcLZTygCGruh0XW+YIWtJbLxLWoDzoc6oL2ZJ19k= X-Google-Smtp-Source: AGHT+IHkqm+uLvXmrUSNcNIJLgiiURW2BAKvzi1iWAoAS5Ti76X413zwYKwnbJopXkGa9T8gjdPcyznRxuHWeLoPoLs= X-Received: by 2002:a81:4992:0:b0:570:654f:aa42 with SMTP id w140-20020a814992000000b00570654faa42mr13891650ywa.40.1691482672546; Tue, 08 Aug 2023 01:17:52 -0700 (PDT) MIME-Version: 1.0 From: Yogesh Mahajan Date: Tue, 8 Aug 2023 13:47:16 +0530 Message-ID: Subject: Request for inputs on #6208 - MaxQueuePool Issue To: pgadmin-hackers Cc: Nikhil Mohite Content-Type: multipart/alternative; boundary="000000000000d76490060264ff1e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d76490060264ff1e Content-Type: text/plain; charset="UTF-8" Hi Hackers, For the #6208 , below 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, SQLAlchemy 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 incorrect 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? @Nikhil Mohite Please add if anything is missed. Thanks, Yogesh Mahajan EnterpriseDB --000000000000d76490060264ff1e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Hackers,

For the=C2=A0= #6208, b<= /span>elow are the observati= ons about this issue=C2=A0-=C2=A0

On each API request, an application db co= nnection is created with state 'idle in transaction'. Connection st= ate 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 an= d response is not sent, the application db connection remains orphaned. Thi= s connection is only reset on application restart.

Issue -=C2=A0In case of pgAdmin,=C2=A0if the user openes 15 query tool tabs & all of the= m have long running transactions like pg_sleep(), then opening new query to= ol/or any operation on pgAdmin which hits API request to backend will throw= 'QueuePool Limit reached' error. (Because=C2=A0by default,=C2=A0SQLAlchemy allows 15 connections total: 5 c= onnections in pool & 10 in overflow and pgAdmin uses default setting.)<= /span> OR =C2=A0if the us= er executes a query in the query tool & while query execution is in pro= gress, hits F5(keyboard shortcut to execute query) 16 times continuously, t= hen pgAdmin throws 'QueuePool Limit reached' error. After that, the= query tool gives incorrect responses to the queries.

Solutions=C2=A0-=C2=A01.Provide configurable set= tings for 'pool_size' & 'max_overflow' parameters for S= QLAlchemy.
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 Null= Pool may impact the performance.=C2=A0

Wh= at approach should be followed to fix=C2=A0the issue?

@Nikhil Mohite=C2=A0Please add if= anything is missed.

Thanks,
Yogesh Mahajan
EnterpriseDB
--000000000000d76490060264ff1e--