Kill infinite loop plperl

Question

How can I kill session with infinite looped plperl function?

Both pg_terminate_backend and pg_cancel_backend had no effect. kill -1 pid did nothing as well and kill -9 pid crashed server.

Answer 1

You are correct,

CREATE FUNCTION foo()
RETURNS void
AS $$
  while(1) {}
$$ LANGUAGE plperl;

SELECT * FROM foo();

Having called pg_terminate_backend and pg_cancel_backend that plperl backend persists. Seems to be a bug that they already know about, from the lists Tom Lane says,

Even assuming that we can fix the garden-variety bugs like these, there's still a fundamental problem that an uncooperative user function (particularly one in plperl/pltcl/plpython) can indefinitely delay response to pg_terminate_backend. Maybe that's okay, seeing that it can similarly hold off or disregard QueryCancel, but I'm not sure the people asking for this are really gonna be satisfied. (One thing we should consider is making ERRCODE_ADMIN_SHUTDOWN unconditionally untrappable by plpgsql exception blocks, which'd at least fix the issue for plpgsql functions.)

I am not sure they have a solution to this or not. From a Craig Ringer,

In general it's safe to use pg_terminate_backend as a "bigger hammer". SIGTERM as sent by pg_terminate_backend() will often, but not always, cause a backend that can't respond to a cancel to exit.

He even goes into explaining some of the options that can cause the hang.

Anyway, let's try something

CREATE FUNCTION foo()
RETURNS void
AS $$
  $SIG{INT} = $SIG{TERM} = sub { die; };
  while(1) {}
$$ LANGUAGE plperl;

Then I ran

SESSION 1                           SESSION 2

SELECT pg_backend_pid();
 pg_backend_pid 
----------------
          20465

SELECT * FROM foo();
-- LOOPING

                                    pg_terminate_backend(20465);


ERROR:  Died at line 2.
CONTEXT:  PL/Perl function "foo"

That seems to work, if it's an option.

Answer 2

I caused an infinite loop with the DO statement below on psql:

DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    RAISE INFO '%', num;
    num := num + 1;
  END LOOP;
END
$$;

Then, it took me about a long time to automatically stop(kill) the infinite loop with the error below:

ERROR: canceling statement due to user request

But, holding Ctrl + C took me only 6 seconds to manually stop(kill) the infinite loop with the error below:

ERROR: canceling statement due to user request

And, I caused an infinite loop with the DO statement below on psql:

DO $$
BEGIN
  LOOP
  END LOOP;
END
$$;

Then, as soon as I pressed Ctrl + C, I could manually stop(kill) the infinite loop with the error below:

ERROR: canceling statement due to user request