Is there a way to detect and cancel a long-running query?

[grafana-dee]

I'm looking to run queries against a data warehouse, some of these will be quick, some slow... depending on the parameters chosen by users operating a dashboard.

If the query is very slow (does not return in less than 30 seconds), I want to cancel the query and instruct the end user to refine their report parameters further.

PostgreSQL gives a way to cancel a query:

SELECT pg_cancel_backend(pid of the postgres process); 

But I do not have the pid to give to the backend.

Is there a way to obtain the pid for a query that has been started by lib/pq, from lib/pq?

[johto]

I've shared my thoughts on this subject here: https://groups.google.com/forum/#!topic/golang-nuts/l9vga1aAsHI

[grafana-dee]

I feel dirty. I have a solution that works, but there is a limitation... I believe that this will only work when you have a single database server (or requires super-user permissions if you need to do it across servers).

In essence it is this:

SELECT pg_cancel_backend(pid)
  FROM pg_stat_activity
 WHERE state = 'active'
   AND query LIKE '--someidentifier%';

You take your usual long-running query:

db.Exec(`SELECT everything`)

And you prepend it with a SQL comment that contains an identifier for that specific instance of that query being run:

db.Exec(`--someUniqueIdentifier
SELECT everything`)

The comment can be seen in:

SELECT query FROM pg_stat_activity;

Which means we can query pg_stat_activity for the pid of that query being run:

SELECT pid
  FROM pg_stat_activity
 WHERE state = 'active'
   AND query LIKE '--someUniqueIdentifier%';

And if we can get the pid, then we can cancel the query in a clean way using pg_cancel_backend:

SELECT pg_cancel_backend(pid)
  FROM pg_stat_activity
 WHERE state = 'active'
   AND query LIKE '--someidentifier%';

So all you really need to do is to set off a goroutine to tick after some set amount of time... if the query returns first ignore the tick. If the tick returns first (after your timeout) then call the SQL that looks up the pid and cancels the query.

As I said, I feel dirty but it works in a way that doesn't involve asking database/sql or lib/pq to do anything new and works for my scenario.

There's still the need for a CancelQuery thing... for those with multi-server instances, etc... but this works for me, now.

[grafana-dee]

A fuller example for anyone in the future hitting this and wondering what this looks like, a snippet:

    queryId := randomString()
    timer := time.NewTimer(30 * time.Second)
    go func() {
        <-timer.C
        db.Exec(`SELECT pg_cancel_backend(pid)
  FROM pg_stat_activity
 WHERE state = 'active'
   AND query LIKE '--` + queryId + `%'`)
    }()

    var total int64
    _, err = db.Exec(`--`+queryId+`
SELECT allTheThings`)

    // Cancel the timer before handling the error or doing anything else
    timer.Stop()
    if err != nil {
        e, ok := err.(*pq.Error)

        if !ok {
            // log the error, return from your func
        }

        switch e.Code.Name() {
        case "query_canceled":
            // return in a way that your user knows it took too long
        default:
            // log the error, return from your func
        }
    }

randomString just generates some identifier for this instance of this query, nothing else in the example should be hard to figure out.

[johto]

As I said, I feel dirty but it works in a way that doesn't involve asking database/sql or lib/pq to do anything new and works for my scenario.

Yeah.. That's a horrible hack if ever I've seen one. I'd rather have gone with an explicit transaction and calling pg_backend_pid() before the query.

There's still the need for a CancelQuery thing... for those with multi-server instances, etc... but this works for me, now.

Probably, but this almost certainly needs some co-operation from database/sql's side.

[tejasmanohar]

FWIW I think this can be done with #535 now.