3

What is the best way to interrupt a long-running query in RPostgresql?

For example, I wanted to see the first 10 rows of a table and meant to type,

  dbGetQuery(con,"
  select * from big.table
  limit 10
  ")

But I sometimes leave out the "limit 10" and then my program runs forever. Hitting ctrl-C or the stop button from my R terminal doesn't work. I either have to wait a long time and then see the full output print or I can abort the R process.

Michele
  • 8,563
  • 6
  • 45
  • 72
d_a_c321
  • 533
  • 1
  • 11
  • 23

2 Answers2

6

You can try to connect to Postgres with psql look for your query in select * from pg_stat_activity and then use select pg_cancel_backend(long_query_pid) to cancel the query.

Or you can use this queries inside R.

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
  • Is there a way to do this within R without going through psql (or RPostgresql)? Strangely, the security is such that I don't have permission to cancel queries (even my own) on the server. The only way I can cancel a query is by closing my client. – d_a_c321 Oct 12 '13 at 17:49
  • @dchandler You can execute this queries from anything that can execute queries. I mentioned `psql` only because it is commonly awailable. As for permission this is what manual sais : "You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser." – Ihor Romanchenko Oct 12 '13 at 18:12
  • 1
    @dchandler Found that the quote from the manual is for 9.2 or later versions. In 9.1 or earlier it is restricted to superusers only. – Ihor Romanchenko Oct 12 '13 at 18:14
  • @IgorRomanchenko Thanks for the suggestion. I think you're first answer is great and would have worked if the permissions permitted it. – d_a_c321 Oct 14 '13 at 00:33
3

Use RPostgres. It fetches the data a row at a time, so you can easily interrupt it at any point.

hadley
  • 102,019
  • 32
  • 183
  • 245