Timeout and cancelation of SQL queries

[Mark McGranaghan]

I'm looking to timeout potentially long-running SQL queries

in a Postgres-backed app. Is there a good way to do this in

the database/sql or lib/pq packages?

Here's an example query that I'd like to time out:

http://play.golang.org/p/LWAdtSunPo

I'd like to control the timeout duration on a per-query

basis (vs on a per-pool basis), but even a pool-level

timeout would be useful.

If necessary I could use a CancelQuery()-type function to

implement a timeout in app code with a spawned goroutine, 

Timer, etc. But I don't see a way to cancel a running query

or close a single connection from the pool in database/sql.

Does something like this exist?

Ideally I'd also like to take advantage of Postgres's

statement_timeout to timeout queries on the server side too.

That would require issuing an SQL statement on a connection

before making the main SQL query on the same connection. Is

there a way to do this in database/sql?

[Henrik Johansson]

Perhaps statement_timeout?

http://www.postgresql.org/docs/9.3/static/runtime-config-client.html

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Henrik Johansson]

Ah a bit quick it seems... sorry about that.

[ma...@joh.to]

On Friday, June 6, 2014 5:56:40 PM UTC+2, Mark McGranaghan wrote:

I'm looking to timeout potentially long-running SQL queries

in a Postgres-backed app. Is there a good way to do this in

the database/sql or lib/pq packages?

Here's an example query that I'd like to time out:

http://play.golang.org/p/LWAdtSunPo

I'd like to control the timeout duration on a per-query

basis (vs on a per-pool basis), but even a pool-level

timeout would be useful.

Controlling it at pool level is trivial; just set statement_timeout in the second argument to sql.Open().

Controlling it on a per-statement level is more difficult.  I think pretty much the only way is to use explicit transactions and SET LOCAL statement_timeout TO ..; but that will also increase the amount of round-trips you need to do to the server, unfortunately.  A workaround might be to have a separate, smaller pool for the queries which need a different statement_timeout.
 

If necessary I could use a CancelQuery()-type function to

implement a timeout in app code with a spawned goroutine, 

Timer, etc. But I don't see a way to cancel a running query

or close a single connection from the pool in database/sql.

Does something like this exist?

That's slightly harder than using statement_timeout, I believe.  You'd have to start a transaction, call pg_backend_pid() to get the pid of the backend attached to that transaction and then run pg_cancel_backend() from another connection when you want to cancel the query.  It should work, but it's a bit ugly.


.marko