Re: pl/tcl function to detect when a request has been canceled

We have occasional need to run very long-running pl/tcl scripts. If the request is cancelled (say, by the user hitting ^c in psql) the server-side script still runs to completion.

There is a C-level variable QueryCancelPending that can be used to monitor for this case, but it’s not visible at the pl/tcl scripting level. This is a simple new command that returns the current state of this variable to Tcl.

We are currently maintaining a fork of pl/tcl at https://github.com/flightaware/pltcl that has this mod, but it would be useful to get the functionality into mainline PostgreSQL.

Hi,

On 2018-06-08 18:08:14 +0000, Peter Da Silva wrote:
> We have occasional need to run very long-running pl/tcl scripts. If
> the request is cancelled (say, by the user hitting ^c in psql) the
> server-side script still runs to completion.

>
> There is a C-level variable QueryCancelPending that can be used to
> monitor for this case, but it’s not visible at the pl/tcl scripting
> level. This is a simple new command that returns the current state of
> this variable to Tcl.
>
> We are currently maintaining a fork of pl/tcl at
> https://github.com/flightaware/pltcl that has this mod, but it would
> be useful to get the functionality into mainline PostgreSQL.

I'm not terribly opposed to this, but I wonder if the much more
pragmatic solution is to just occasionally call a database function that
checks this? You could just run SELECT 1 occasionally :/

- Andres

Andres Freund <andres(at)anarazel(dot)de> writes:
> On 2018-06-08 18:08:14 +0000, Peter Da Silva wrote:
>> There is a C-level variable QueryCancelPending that can be used to
>> monitor for this case, but it’s not visible at the pl/tcl scripting
>> level. This is a simple new command that returns the current state of
>> this variable to Tcl.

> I'm not terribly opposed to this, but I wonder if the much more
> pragmatic solution is to just occasionally call a database function that
> checks this? You could just run SELECT 1 occasionally :/

That would be quite expensive for the purpose, surely.

My complaint about it is more like "if we're going to put this into
pltcl, why not also plperl and plpython?" It might be unfair to
ask this patch to cover all three, but it would be odd not to try to
maintain feature parity ... especially since pltcl is probably the
least-used of the three these days.

regards, tom lane

On 6/8/18, 1:12 PM, "Andres Freund" <andres(at)anarazel(dot)de> wrote:
I'm not terribly opposed to this, but I wonder if the much more
pragmatic solution is to just occasionally call a database function that
checks this? You could just run SELECT 1 occasionally :/

That seems to work, and I suppose in most cases the overhead could be mitigated by only calling it every N times through a loop, but it's not as clean.

On 2018-06-08 14:41:41 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2018-06-08 18:08:14 +0000, Peter Da Silva wrote:
> >> There is a C-level variable QueryCancelPending that can be used to
> >> monitor for this case, but it’s not visible at the pl/tcl scripting
> >> level. This is a simple new command that returns the current state of
> >> this variable to Tcl.
>
> > I'm not terribly opposed to this, but I wonder if the much more
> > pragmatic solution is to just occasionally call a database function that
> > checks this? You could just run SELECT 1 occasionally :/
>
> That would be quite expensive for the purpose, surely.

Sure, but it works today...

Either way, I'm not convinced that handling query cancels in isolation
is really the right thing. I think pretty much all forms of interrupt
would need to be processed, not just cancels. It's imo at least as
important to process session termination (including recovery conflicts),
and catchup interrupts. I think we largely require that the PLs handle
exceptions anyway, so just having a 'pg_process_interrupts()' function
that then is wrapped by the individual PLs would make sense imo.

Greetings,

Andres Freund

On 6/8/18, 1:12 PM, "Andres Freund" <andres(at)anarazel(dot)de> wrote:
I'm not terribly opposed to this, but I wonder if the much more
pragmatic solution is to just occasionally call a database function that
checks this? You could just run SELECT 1 occasionally :/

After further discussion with our team:

Would this work if the reason for it ignoring the cancel request is that it is already performing a long-running spi_exec with a large response?

On 2018-06-08 19:16:49 +0000, Peter Da Silva wrote:
> On 6/8/18, 1:12 PM, "Andres Freund" <andres(at)anarazel(dot)de> wrote:
> I'm not terribly opposed to this, but I wonder if the much more
> pragmatic solution is to just occasionally call a database function that
> checks this? You could just run SELECT 1 occasionally :/
>
> After further discussion with our team:
>
> Would this work if the reason for it ignoring the cancel request is
> that it is already performing a long-running spi_exec with a large
> response?

Not sure I quite understand what you mean. You're thinking of the case
where you're processing rows one-by-one with a cursor? Or that a single
spi call takes a long while to process the query?

Greetings,

Andres Freund

Andres Freund <andres(at)anarazel(dot)de> writes:
> Either way, I'm not convinced that handling query cancels in isolation
> is really the right thing. I think pretty much all forms of interrupt
> would need to be processed, not just cancels.

+1

regards, tom lane

On 6/8/18, 2:21 PM, "Andres Freund" <andres(at)anarazel(dot)de> wrote:
Not sure I quite understand what you mean. You're thinking of the case
where you're processing rows one-by-one with a cursor? Or that a single
spi call takes a long while to process the query?

The former, I believe. One example (lightly obfuscated):

spi_exec -array a "SELECT this,that from table where stuff..." {
if {$use_cancel_pending && [cancel_pending]} {
error "cancelled in ..."
}
do_something_with_array a
more_business_code_here
and_so_on...
}