Describe the bug

I set the query history limit to 5000 (MAX_QUERY_HIST_STORED=5000), which in my case would keep about a week of history when I do a lot of queries. After accumulating history for a while, including some large history items, over 10 MB each, pgAdmin started crashing to desktop when accessing the query history tab.

To Reproduce

Steps to reproduce the behavior:

1. Set MAX_QUERY_HIST_STORED=5000
2. Open pgAdmin
3. Connect to a database
4. Open a query tool
5. Run thousands of queries on the same database, including some large SQL scripts, around 15 MB of SQL; by script I mean SQL queries separated by semicolons. You may want to start with the large scripts first, in case the history item size the trigger for the problem, not the number of history entries.
6. Attempt to access the query history tab - the app will crash
7. Reopen the app
8. Connect to the same database
9. Open a query tool
10. The app will try to show the query history, then it will crash again

Expected behavior

The app shouldn't crash to desktop and should display the query history.

Error message

There is no error message. The app just closes, after showing the "Fetching history..." message.

Screenshots

This is how it looks before crashing:

Desktop (please complete the following information):

• OS: Windows 11 10.0.22621.1992
• Version: 7.5 ; the issue can also be reproduced on pgAdmin 4 v7.3
• Mode: Desktop
• Browser (if running in server mode): N/A
• Package type: Windows exe

Additional context

The log doesn't contain anything interesting, just these two lines recorded at each startup:

2023-08-08 16:45:27,358: WARNING	werkzeug:	WebSocket transport not available. Install simple-websocket for improved performance.
2023-08-08 16:45:27,358: WARNING	werkzeug:	Werkzeug appears to be used in a production deployment. Consider switching to a production web server instead.

The Windows Event Viewer doesn't seem to show anything about the crash either.

The problem is 100% reproducible on my system.

I checked the query_history table from pgadmin4.db, there are currently 9582 records in the table, and 2359 of those records seem to be history entries from the affected database.

Unfortunately it seems that at some point pgAdmin stopped storing query history in JSON format in this table, which makes it harder to access the history with a SQLite database browser when the app is unable to do it. I now have to use a hex decoder to access the JSON from the pgadmin4.db database.

I'm not sure if the issue is caused by the number of items in the history, or the size of some of the items in the history, or both.

Running

select max(length(query_info)) from query_history;

returned 27324404. Since each char is encoded as two when converted to hex, it seems the JSON with the history for my largest SQL script is 13662202 bytes long. So the largest SQL script from the history is a bit under 14 MB (28 MB hex encoded).

Running

select sum(length(query_info)) from query_history where sid = 18;

returns 122770206, so around 123 MB total hex encoded, which would be around 62MB of JSONs. sid 18 is the affected database. The other databases only have about 6MB of hex history in total (3 MB of JSON).

Since fixing this issue might take a while, what would be an workaround for this problem, that wouldn't involve losing the entire history? I could try to clear the larger entries from the SQLite query_history table, but I'm not sure if that could cause problems or not, and if I need to update the srno column as well after deleting the large items.