How do I handle interrupts (i.e. SIGTERM or SIGINT)

I have a function that does quite a bit of intensive processing. Once in awhile I need to be able to stop it (i.e. for DB maintenance) even if it's mid-run. I'd like to send it a SIGINT so it can wind up what it's doing gracefully. What I found was as soon as I introduce a normal perl interrupt handler, the function stops responding to interrupts properly.

Working

CREATE OR REPLACE FUNCTION run_for_awhile() RETURNS void
    AS $_X$
        spi_exec_query('select pg_sleep(30)');
$_X$
LANGUAGE plperlu;

If I do select run_for_awhile() in psql, I can type Ctrl+C and it cancels. In the actual app I'd be doing select pg_cancel_backend(PID) but my tests have the same outcome via that method.

If I modify the function to capture SIGINT and/or SIGTERM, the signal gets sent, but the function doesn't notice it until after 30 seconds (when pg_sleep finishes). So the handler does run eventually but not "immediately".

i.e.

CREATE OR REPLACE FUNCTION run_for_awhile() RETURNS void
    AS $_X$
        $SIG{INT} = sub { die "Caught a sigint $!" };

        spi_exec_query('select pg_sleep(30)');
$_X$
LANGUAGE plperlu;

Solution

What you'll need to do here is have your Perl interrupt handler invoke the same C code as the CHECK_FOR_INTERRUPTS() macro does, that is:

    if (InterruptPending)
        ProcessInterrupts();

(If you're on windows you need a bit more, see src/include/miscadmin.h).

Unfortunately there's no wrapper function you can call trivially, and plperl only seems to invoke CHECK_FOR_INTERRUPTS in plperl_spi_prepare.

So one option you have seems to be to set a global in your Perl interrupt handler that makes your app's main loop do a SELECT 1 when it sees the variable is set. Bit ugly, but it should work.

Otherwise you can possibly use Perl's Inline::C or similar to invoke ProcessInterrupts.

It'd be nice if plperl exposed a Perl wrapper for CHECK_FOR_INTERRUPTS(). Consider submitting a patch.