postgresqlplpgsql

Catch pg_cancel_backend in plpgsql


I understand that I can't error-handle pg_terminate_backend in PL/pgSQL. But I am surprised that I can't error-handle pg_cancel_backend.

Below is one attempt that does not work, but I also tried similar ways in my code and had no success to catch it. Is there really no way to catch it in PL/pgSQL?

CREATE OR REPLACE FUNCTION deleteme_try_sleep() RETURNS void AS $$
BEGIN
  PERFORM pg_sleep(600);
END;
$$ LANGUAGE plpgsql;


DO $$
BEGIN
  RAISE NOTICE 'Calling sleeper...';
  BEGIN
    PERFORM deleteme_try_sleep();
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'Inner call failed: %', SQLERRM;
  END;
  RAISE NOTICE 'Block continued';
END;
$$;

-- use pg_cancel_backend on the above

Solution

  • As the documentation says:

    The special condition name OTHERS matches every error type except QUERY_CANCELED and ASSERT_FAILURE. (It is possible, but often unwise, to trap those two error types by name.)

    So you would have to use

    BEGIN
       ...
    EXCEPTION
       WHEN OTHERS OR QUERY_CANCELED THEN
          ...
    END;