1

I'm using QSqlDatabase and am trying to find a way to kill a running query (kind of a necessary feature of the sql client I'm writing)

Is there a way to get the id for a running query? (the plan is to just execute a kill/pg_cancel_backend command in another connection -- needs to support PostgreSQL + MySQL)

I've considered snooping the process list for an identical query, but what if the reason it's being terminated is because it's running twice and the user just wants to kill a specific one? (that would make that method not exactly accurate and also would be super hacky)

Edit: I'm not super tied to QSqlDatabase (other than that it's part of the framework I'm using) so I don't mind using something else if it's the only way, but it sure would be nice to be able to use it.

I have absolutely no ideas, so anything would be super appreciated!

Chelsea Urquhart
  • 1,388
  • 1
  • 11
  • 18
  • 1
    Personally I don't advise trying to use Qt's SQL classes for anything more than rudimentary database work. They're just missing too much functionality, including [some truly amazing oversights](https://bugreports.qt.io/browse/QTBUG-40586). Maybe `number()` is supposed to be the SQLState, but if so you're in trouble if it's alphanumeric... – Craig Ringer Aug 24 '15 at 00:48
  • 1
    (Seems fixed in Qt5). Lack of access to database-specific data type mappings/extensions, lack of access to proper error fields, limited metadata, etc etc. It's painful. This is one area where Qt should've ripped off most of the relevant Java API, JDBC, and made a big mistake by not doing so. – Craig Ringer Aug 24 '15 at 01:03
  • I'm using 5.5 so looks like that's not an issue, at least for getting at SQLSTATE. I did not realize all of that extra metadata existed... Can you recommend a good library or libraries? I guess the libmysqlclient and libpq c libraries? – Chelsea Urquhart Aug 24 '15 at 06:29
  • Using `libpq` etc is one option. Otherwise you can just work within QtSQL and live with its limitations ... or send patches to Qt to fix them. You'll find that via QtSQL you probably have quite limited parameter binding, limited information about the data types of fields returned in query result sets, etc. So it all really depends on what you actually need to do. – Craig Ringer Aug 24 '15 at 06:32

1 Answers1

1

In Postgres you can identify your backend process id by executing

select pg_backend_pid();

just after a connection is established. The id remains unchanged until you close the connection.

The MySQL equivalent of this function is CONNECTION_ID ().

klin
  • 112,967
  • 15
  • 204
  • 232
  • 1
    ... and you can then make another connection and execute `SELECT pg_cancel_backend('the_id');` to cancel it. What libpq does is pretty much an optimised version of this. – Craig Ringer Aug 24 '15 at 00:48
  • Wow, I did not realize the query id was actually the connection id. That is so much cleaner than I was worried I was going to have to do, thanks so much! – Chelsea Urquhart Aug 24 '15 at 05:51