Re: Request for inputs on #6208 - MaxQueuePool Issue - Mailing list pgadmin-hackers

From Akshay Joshi
Subject Re: Request for inputs on #6208 - MaxQueuePool Issue
Date
Msg-id CANxoLDfHoprxmZ0ej8e2D1EWjswt3mv4ewCsBUtwsdELFQ8qhw@mail.gmail.com
Whole thread Raw
In response to Request for inputs on #6208 - MaxQueuePool Issue  (Yogesh Mahajan <yogesh.mahajan@enterprisedb.com>)
Responses Re: Request for inputs on #6208 - MaxQueuePool Issue
List pgadmin-hackers
Hi Yogesh

On Tue, Aug 8, 2023 at 1:48 PM Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:
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?

    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

pgadmin-hackers by date:

Previous
From: Yogesh Mahajan
Date:
Subject: [pgadmin-org/pgadmin4] 0b24a8: Ensure keyboard shortcut for query execution is di...
Next
From: Akshay Joshi
Date:
Subject: [pgadmin-org/pgadmin4] da71c0: Fixed an issue where the 'CASCADE' keyword is alwa...