How to cancel long-running QSqlQuery?

Question

How to cancel long running QSqlQuery?

Database is returning 3M+ rows and it's shown in QTableView control. I'd like to be able to force stop both long operations:

when database is running a long operation
if database is fast, but there is a huge number of rows to be returned and processing/copying/showing those takes a lot of time

2nd bullet, can be solved by not using QSqlQueryModel. In this case, parsing query results manually can be done in stages and this will be implemented, but i'd also like to know if the process of moving data DB->QTableView can be interrupted and cancelled.

I've tried following without success:

QSqlQuery::finish()
QFuture::cancel()
QSqlDatabase.close() -- this one crashes application

If full context is needed, it's here. Method in question is

on_button_stopQuery_released

dom0 · Accepted Answer · 2018-08-17T09:15:55.927

Aborting queries during execution (not fetching, which is what QSqlQuery::finish does) is hit-and-miss in all databases. Qt itself doesn't support this; workarounds will be backend-specific.

For example, with PostgreSQL you can do the following:

In your original connection, retrieve the connection ID (SELECT pg_backend_pid();) and save it
When you want to abort your query, open a second connection and kill the query by issuing SELECT pg_cancel_backend(saved_id);

SQLite has sqlite3_interrupt(sqlite3*). This interrupts queries and does not close the connection.

MySQL is similar to PostgreSQL:

First retrieve the connection ID (SELECT CONNECTION_ID();)
Then kill it through another connection (KILL [CONNECTION|QUERY] $connection_id).

As you can see, even the capabilities provided are backend-specific. Postgres can only abort connections, while SQLite can only abort queries. The easiest way to implement this would thus be to discard the connection if the query was aborted and the connection is still valid. Then you can have a simple two-API interface for a cancellation management (pseudo code, i.e. Python):

class IConnectionCancellation:
    def register(connection):
        # save/retrieve connection ID

    def cancel():
        # open second connection, send backend-specific query

For large result sets, consider using canFetchMore and fetchMore in your model. That way you don't have to process the entire result set before showing some results to the user; might feel smoother to use. Doesn't help with inherent query execution latency due to e.g. order by or grouping clauses, of course.

would fetchMore actually work with postgres? documentation says that it only works with databases which do not report dataset size (i.e. rowcount). in postgres it seems to load all rows... — mspehar, Aug 11 '18 at 21:39

How to cancel long-running QSqlQuery?

1 Answers1