Hi Yogesh On Tue, Aug 8, 2023 at 1:48 PM Yogesh Mahajan < yogesh.maha...@enterprisedb.com> wrote:
> Hi Hackers, > > For the #6208 <https://github.com/pgadmin-org/pgadmin4/issues/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? > If NullPool *may* impact the performance then we should go with Solution 1. > > > @Nikhil Mohite <nikhil.moh...@enterprisedb.com> Please add if anything is > missed. > > Thanks, > Yogesh Mahajan > EnterpriseDB >
