Connection and stability issues since v7, possibly related to background schema changes #6514
Description
Describe the bug
When trying to view all data in a large-is table (70,000 rows).
Sometimes it will be successful the first time, then it just keeps failing more often than not. The UI pauses for a moment before showing the connection warning dialog stating that the application has lost the DB connection. This message pops up and in the background you can see in the query messages output it says "Query successful" but no data is rendered.
These are the logs output before clicking the button to reconnect and continue:
In pgadmin4 logs:
2023-06-29 16:28:57 2023-06-29 15:28:57,666: ERROR pgadmin: Failed to execute query (execute_async) for the server #1 - CONN:8825313(Query-id: 8380309):
2023-06-29 16:28:57 Error Message:ERROR: message contents do not agree with length in message type "D"
In postgres logs:
2023-06-29 16:28:57 2023-06-29 15:28:57.651 UTC [5222] LOG: could not send data to client: Connection reset by peer
2023-06-29 16:28:57 2023-06-29 15:28:57.651 UTC [5222] FATAL: connection to client lost
After clicking the button, it tries again and I get the "'NoneType' object has no attribute 'pgconn'" error in the message output. In the pgadmin4 logs I get this:
2023-06-29 16:32:04 2023-06-29 15:32:04,512: ERROR pgadmin: 'NoneType' object has no attribute 'pgconn'
2023-06-29 16:32:04 Traceback (most recent call last):
2023-06-29 16:32:04 File "/venv/lib/python3.11/site-packages/flask/app.py", line 1823, in full_dispatch_request
2023-06-29 16:32:04 rv = self.dispatch_request()
2023-06-29 16:32:04 ^^^^^^^^^^^^^^^^^^^^^^^
2023-06-29 16:32:04 File "/venv/lib/python3.11/site-packages/flask/app.py", line 1799, in dispatch_request
2023-06-29 16:32:04 return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
2023-06-29 16:32:04 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2023-06-29 16:32:04 File "/venv/lib/python3.11/site-packages/flask_login/utils.py", line 290, in decorated_view
2023-06-29 16:32:04 return current_app.ensure_sync(func)(*args, **kwargs)
2023-06-29 16:32:04 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2023-06-29 16:32:04 File "/pgadmin4/pgadmin/tools/sqleditor/__init__.py", line 893, in poll
2023-06-29 16:32:04 status, result = conn.poll(
2023-06-29 16:32:04 ^^^^^^^^^^
2023-06-29 16:32:04 File "/pgadmin4/pgadmin/utils/driver/psycopg3/connection.py", line 1478, in poll
2023-06-29 16:32:04 if not self.conn.pgconn.is_busy():
2023-06-29 16:32:04 ^^^^^^^^^^^^^^^^
2023-06-29 16:32:04 AttributeError: 'NoneType' object has no attribute 'pgconn'
There seems to be a similar connection related issue with the Query Tool. After a short period of usage, I start getting a situation where all queries I run appear to take ~40ms but no data is actually returned. No query is even made to postgres, pgadmin4 appears to just return a success status without having performed any action at all. When this happens the only solution is to open a new Query Tool window.
I have a feeling that the bug is more easily triggered when running commands that alter the database schema such as CREATE/DROP table and partition commands, but I have the bug trigger in just about every kind of query. I do wonder though if underlying schema change could be causing an issue due to caching of object IDs or prepared statements.
Desktop (please complete the following information):
- OS: Docker (macOS host)
- Version: 7.4
- Mode: Server
- Browser (if running in server mode): Chrome
- Package type: Container
Additional context
I don't have any problems if I downgrade to v6. This is has only been a problem in v7.