On Wed, 9 Aug 2023 at 08:25, Akshay Joshi <akshay.jo...@enterprisedb.com> wrote:
> 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. > Yes, with a much larger default value I would suggest. Plus, we should also stop leaving orphaned connections behind... -- Dave Page Blog: https://pgsnake.blogspot.com Twitter: @pgsnake EDB: https://www.enterprisedb.com
