1

I am using psql to query a large database. and (understandably) many queries that I type in take a long time. I typically want to cancel these queries and so I enter ^C, and psql responds with the message

Cancel request sent

This cancel request sometimes cancels queries quickly (milliseconds), but sometimes takes very long times (>10 minutes) to cancel the query and display a prompt where I can type in new commands. These long cancel times even happen on queries that have only been running less than a second.

Why does psql take so long to cancel some queries? And what can I do to make psql cancel the queries faster?


Version info:

$ psql --version
psql (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)
  • 2
    There's a lot of machinery between your keyboard and the database server (even if the server is local). An analogy would be launching a satellite: depending on its trajectory there will be only a few points at which you can command it to return to base if you change your mind. So, think twice before you run the query; you can't do anything to "cancel the queries faster", unless you're willing to restart Postgres (and even then it won't be as fast as you think). – mustaccio Apr 1 at 22:14
  • @mustaccio People regularly ask about how to speed up queries, and cancelling queries should be no slower than successful queries, so I think this is still a reasonable question. Also, I can easily start a new instance of psql, use pg_terminate_backend to cancel the previous query, and then reissue a new query in the same psql instance. It would be nice to not have to resort to those measures though. – Mike Izbicki Apr 1 at 23:55
  • You are entitled to your opinion, however unsubstantiated it is. – mustaccio Apr 2 at 1:31
  • @mustaccio If taking >10 minutes to cancel a query is expected behavior, then I would like to learn the details about why, as this is very counterintuitive to me. – Mike Izbicki Apr 2 at 2:42
  • Strongly suggest you ask the Postgres developers why this is. Not all questions are best asked here. When you hear back from them, you can answer your own question with your findings. – Colin 't Hart Apr 2 at 8:58
1

I usually see this when the resultset is large, and has already started to be sent to the client. The client (apparently, I have not dug into the source code of this) still needs to read everything that was in the pipeline to be sent to it but not yet received, and this can take a long time, especially if the client is in the midst of a swap storm.

Are you using any third party or little-used extensions?

If these aren't the case for you, you will need to provide a reproducer or do some investigation of your own. While you wait for the cancel to take effect, what is busy on your system? Just the psql process itself, just the postgres backend it was connected to, or both? Can you run perf on the busy process and see what it is doing? Or attach gdb to it and get some backtraces at random times?

| improve this answer | |
0

Unless your query (which you didn't show) calls C code from third-party extensions that forgot to call CHECK_FOR_INTERRUPTS regularly, this is a PostgreSQL bug that you should report. Include the statement and its execution plan in the report.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.