Query tool async database cursor never freed

[simplect]

Describe the bug

We host a pgadmin server instance for students to learn to work with big datasets. We noticed that when loading in the dataset (1 million rows) the full dataset was loaded in the RAM and this is never released until the server crashes (30gb+ usage).

After looking into other issues like #5797 it was clear that part of this was expected behavior. But the data should still be cleaned up. I found a way to patch it and i found another bug that might be related.

Ducktape fix:
Add this on line 1585 (cancel_transaction) in web/pgadmin/utils/driver/psycopg3/connection.py

        # Release async cursor
        if self.__async_cursor is not None:
            self.__async_cursor.close()
            self.__async_cursor = None

Another error in the logs I encountered was that seemed related to this issue:
File "pgadmin4/pgadmin/utils/driver/init.py", line 34, in ping\n DriverRegistry._objects[type].gc_timeout()\n File "pgadmin4/pgadmin/utils/driver/psycopg3/init.py", line 253, in gc_timeout\n if curr_time - sess_mgr['pinged'] >= session_idle_timeout:\n ~~~~~~~~^^^^^^^^^^\nKeyError: 'pinged'", "level": "ERROR"}

Ducktape fix:
Add if not 'pinged' in sess_mgr or to pgadmin/utils/driver/psycopg3/__init__.py on line 253.

These are probably not the right fixes so i'll leave the right implementation over to you.

To Reproduce

Steps to reproduce the behavior:

1. Go to query tool
2. select * from big_table
3. close and wait

Expected behavior

The RAM should return to < 1GB after a few hours of inactivity and no open tabs.

Desktop (please complete the following information):

• OS: Ubuntu
• Version: 8.13
• Mode: Server
• Browser (if running in server mode): Firefox
• Package type: Python

[pravesh-sharma]

Hi @simplect,

Can you please raise the PR with your changes so we can evaluate them?

[simplect]

Sure, but I have such little experience with the codebase that I highly doubt this is the right fix. But I will raise it.