I've noticed that if you start a long query.. let's say a select that might take 5 or 10 minutes, then you change tabs from the SQL tab to the Object tab, and maybe click on a table, the UI will totally freeze. I won't even be able to get back to the SQL tab. I imagine what is happening is when you go to the other tab and click on a table, it's going to try and interrogate the database for the table definition, get stuck waiting for the database in progress waiting for a query, and then the GUI callback doesn't return.
I think what you need to be doing, is keeping some flag, probably protected by a thread lock, that says when the database is busy, and populate the right pane of the Object tab with an error message "Waiting for query, try later" or something, when the flag says the database is busy. Any option is really better than freezing the UI. For one thing, you can't hit the cancel button on the query if you can't get back to that pane.
I suppose you are experiencing this problem on Oracle. At least I was able to reproduce it on Oracle.
SQuirreL already has a feature that's addressing this and similar problems. But the feature needed improvements to keep the UI from hanging in your scenario. These improvements are available in the latest snapshot (20210922_2129). Here's the according change log excerpt :
Beware that this feature still is not able to make the UI work properly but it keeps it from freezing.
By the way I'm ever a bit disappointed that the connections of several drivers have this kind of threading behavior. For Oracle while a query is running the only method in a connection's scope that works seems to be the cancel method of java.sql.Statement.
Yes it was oracle, and that's good there is some kind of workaround. What behavior would you wish the driver had? Pausing to wait is seemingly the only behavior that gives a reliable consistent result. It could fail immediately, which would mean some scenarios you'd have to loop around retrying, which would be bad.
What I'm tempted to argue is that squirrel should have 2 connections, one for metadata, and one for potentially long running queries. Or just a thread pool with several connections.
As java.sql.Statement provides a cancel method I rather excepted those (except from the cancel method itself) to block during execution. But it seems that's wrong in general.
To use more than one connection in a Session is an interesting idea. I think I'll work on that after the 4.3.0 release. I believe it should be configurable and easy to monitor.
SQuirreL now supports multiple connections. Change log entry:
Query connection pool.
See Session status bar and menu File --> New Session properties
--> tab SQL --> section "Avoid application hangs/delays"
Often JDBC drivers block calls to a database connection when a SQL is being executed.
By default a SQuirreL Session uses a single connection. That is why UI hangs may occur
when the database connection is accessed during SQL execution. To avoid such hangs
SQuirreL now allows a 'query connection pool' of additional connections
which are used for SQLs executed from within the SQL editor only.
Furthermore the pool allows parallel SQL execution even if the connection blocks during execution.
Note: To keep a Session from blocking itself the pool is switched off when autocommit is switched off.
This change was inspired by the discussion in bug #1488.
Could be just me, but I find the new "connection pool" implementation rather problematic. With auto-commit there are session/connection specific settings one may apply and then be surprised to find out (or likely never be able to figure out) they are not effective because of some obscure logic opening a new underlying connection to the database. These include but may be not limited to:
Most of the other SQL clients I've seen approach this issue by maintaining DB connection per SQL sheet, and one "service" connection for any other purpose such as listing the DB object tree and possibly cancelling/killing long-running/stuck queries in the active SQL sheets.
SQuirreL has traditionally bound one session to one DB connection. Dunno if it is feasible to retrofit "New SQL Worksheet/Tab" and "New Session" to basically the same, but the rather unconventional "connection pool" (for a JDBC client) machinery is more problematic than it solves any practical issue, in my opinion. Maybe much simpler check to not allow executing multiple quieries in a single session would be better. Then possibly use a separate "service" connection (that may be shared among all sessions of the same alias) for Object tree purposes which would avoid unnecessart freezing.
Stanimir, some points on your comment:
Not wanting to resurrect an old issue, but.... I was thinking about this today,... are SQL executions done in a new thread? Because in Java right, anything that takes a while shouldn't be done in the GUI thread, it should spawn off a thread... but if it is doing that, why would these freezes happen?
Along the same lines, I was pondering how the SQL editor freezes a lot... especially when pasting code in there... I was thinking about whether it warranted a bug report, and was looking through the options, and I can see "meta data loading timeout", and it occured to me that when you paste code into the editor, to verify that SQL, it potentially has to go to the database to check what columns exist etc. So is it going to the database synchronously in the gui thread? If so, is that why it freezes a lot? And if so, can't it do that in the background, do analysis on what is in the window, and only when the analysis is complete, then go and color it in the main thread?
You may want to try out SQuirreL's option to load columns in background. See menu File --> New Session Properties --> tab SQL --> section "Column loading"