Re: BUG #13518: CancelRequest lacks statement identifier

Dear Tom, Kevin et al,

> The bigger picture though is that what you're suggesting would require
> that a whole new connection be made in order to issue a Cancel.

Am I misunderstanding something? My experience with using Postgres, and
my reading of the documents, is that a new statement, but not a new
connection, is needed to cancel a statement on that connection. The
cancel is signalled by a fresh statement using the same connection (and
specifically that connection's PID and Key). I do not see why a new
connection would enter into it.

The point is that in any situation other than explicit user intervention
- or a built-in post-send-cancel delay - issuing a cancel seems too
risky to do, because the cancel could kill the _next_ statement, not the
one intended. The motive for issuing a cancel - at least in my scenario
- is to increase the probability that the current statement will end as
soon as possible, so the next can run sooner. Any risk of cancelling
that next statement that we're keen to run sooner is very
counterproductive (as would be making the system wait after issuing a
cancel for some guessed time delay after which for sure it will have
been processed). Hence the desire to include some identifier of the
statement we wish to cancel, so either that intended statement is
cancelled or nothing happens.

More generally, I'm puzzled what the scenario is for an app driving
postgres to use cancel automatically. Why cancel at all except for
performance, yet how should a system cancel and move to next statement
performantly without risking next-statement cancel?

> Would it be sufficient for your purposes to cancel a *transaction*
> rather than a *statement*?

No, AFAICS; there is no reason why the statement needing cancelling
would necessarily be in a transaction, and conversely it could be inside
a transaction there was no desire to cancel.

This is not an immediate major hold-up for us at the moment, because
we've just removed the code that was exploiting cancel, choosing
functional certainty over the occasional performance gain of cancelling
a long query. However the experience left me puzzled how to use cancel.

Thanks for thinking about this
Niall Ross

>Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
>
>
>>"niallfr(at)btinternet(dot)com" <niallfr(at)btinternet(dot)com> wrote:
>>
>>
>>>If CancelRequest could include a statement name (or other means
>>>of identifying a statement) and did nothing if that statement was
>>>no longer running by the time the server processed it, the
>>>usability of CancelRequest would be significantly enhanced.
>>>
>>>
>
>
>
>>Would it be sufficient for your purposes to cancel a *transaction*
>>rather than a *statement*? There is already a virtual transaction
>>ID exposed in pg_locks, and it could probably be added to
>>pg_stat_activity; we could probably create a
>>pg_cancel_transaction() function that took a text representation of
>>that and only canceled the transaction if it was running. There
>>would need to be locking on a heavily contended lock or two to make
>>that happen correctly, but presumably this would not ba a
>>high-volume activity.
>>
>>
>
>If I'm correctly visualizing what you've got in mind, it seems like
>that would only narrow the race-condition window not close it entirely.
>I don't believe there's any guarantee that signals-in-flight are delivered
>immediately, so the SIGINT might still arrive at the target process after
>it's finished out the transaction that was meant to be canceled.
>
>The bigger picture though is that what you're suggesting would require
>that a whole new connection be made in order to issue a Cancel. That's
>kind of an expensive solution ... not that what we're doing now is
>exactly free, but at least it doesn't fail if you're up against the
>MaxBackends limit, for instance.
>
> regards, tom lane
>
>
>
>