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

Question

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;

Hello there, perhaps the guys on http://dba.stackexchange.com can help you faster? — Jorge Campos, Sep 25 '17 at 23:00

score 2 · Accepted Answer · answered Sep 26 '17 at 01:45

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.

Thanks Craig, I'm not going to go that deep because I found a reasonable alternative... But this is exactly the kind of answer I was looking for! — Ryley, Sep 26 '17 at 16:56

score 0 · Answer 2 · answered Sep 26 '17 at 16:55

I haven't been able to get completely to the bottom of this, but I found a way around it. SIGINT and SIGTERM are not available, so don't use them. But I can send another interrupt successfully, like SIGPROF. Once that's sent, follow up with SIGINT and everything goes as expected.

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

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

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

2 Answers2