Kill infinite loop plperl

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.

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.

StackExchange Database Administrators Q#213399, answer score: 5