2

When a user aborts his request mid-flight, I sometimes cancel any read-only statements that support whatever read-only operation he was attempting at the time - I follow this pattern sparingly - reserved only for expensive queries that also occur during high traffic periods therefore having the potential to cause contention.

In PG, I cancel queries like so:

SELECT pg_cancel_backend(pid);

When I do so PG complies but logs this: Error: canceling statement due to user request.

I get why it's sibling pg_terminate_backend would classify itself as an Error; it's a last-ditch, potentially dangerous attempt that force-kills the process.

But I don't understand why a usual query cancellation is treated the same. I've requested the cancellation after all, so why is it logged at such a high severity level?

0

2 Answers 2

5

According to the SQL standard, the database engine must communicate, via the SQLSTATE variable, the condition of every completed command, and the value of SQLSTATE indicates precisely how the command completed. The standard classifies SQLSTATE values into classes; a code from each group can and will be treated differently by the client.

The specific condition in question, "query_cancelled", is assigned the code 57014, which belongs to the "errors" class. Appropriately, when psql sees SQLSTATE 57014 in response to its command, it treats this as an error, printing a suitable message.

postgres=# select pg_sleep(50);
ERROR:  canceling statement due to user request
postgres=# \echo :SQLSTATE
57014
1
  • 1
    Just a small FYI, the logs from my OP comes directly from our web service - not psql. I don't use psql; perhaps it's the driver itself that's doing the same code-to-label translation that psql does. Thank you for the detailed breakdown but I'll point out that I found your initial comment of "It's an error from the perspective of the process that issued the cancelled statement" to be a more effective explanation. Commented May 22 at 20:22
1

You are getting this behaviour because from the point of view of the client program which started the SELECT query, it being cancelled is an error.

By default, it is not clear at all that the user who started the SELECT (or whatever command it may have been) was the one who ran the pg_cancel_backend call. It could have been a different user (with the same unprivileged account), or a superuser. Assume I, as a DBA, cancel your query without you knowing or aggreeing; you would certainly wish to get a poignant error message about this, not just a little notice in a corner of your window that there were 0 results, or something like that.

Conceptionally, what would be the result if an error was not reported? The client would then expect some result set - a number of records, or an empty set. Both would be the wrong return values - they would suggest that there were some result there which it is not. Also, of course, if it is an actual program, client-side (and not a user session in some SQL tool) you certainly would want to start any exception processing as usual, to stay consistent.

3
  • That's what @mustaccio above said so in a comment; I got brainfried and forgot that from PG's perspective I'm a different person in each session. The logging itself isn't something I considered problematic (when posting this of course, not now). It's the severity level that threw me off. "Error" is a, well.. "severe" level. I would expect it would be logged as a NOTICE. PG doesn't have first-class support for query cancellation so anything that aborts the command is considered an Error. Commented May 23 at 12:40
  • What I'm trying to say here is while my OP got answered by that comment (and your answer), the matter of fact is that PG would still consider it an Error even if PG had no problem figuring out i'm the same user that issued the statement. Commented May 23 at 12:44
  • Exactly. mustaccios answer is fine too, just wanted to give the different angle of why it all makes sense even if you do not know about the SQL standard. :) @nicholaswmin
    – AnoE
    Commented May 23 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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