That won't work really well with something like I use to do when testing this patch, namely:
postgres=# select pid, array(select pg_cmdstatus(pid, 1, 10)) from pg_stat_activity where pid<>pg_backend_pid() \watch 1
while also running pgbench with -C option (to create new connection for every transaction). When a targeted backend exits before it can handle the signal, the receiving process keeps waiting forever.
no - every timeout you have to check, if targeted backend is living still, if not you will do cancel. It is 100% safe.
But then you need to make this internal timeout rather short, not 1s as originally suggested.
can be - 1 sec is max, maybe 100ms is optimum.
The statement_timeout in this case will stop the whole select, not just individual function call. Unless you wrap it to set statement_timeout and catch QUERY_CANCELED in plpgsql, but then you won't be able to ^C the whole select. The ability to set a (short) timeout for the function itself proves to be a really useful feature to me.
you cannot to handle QUERY_CANCELED in plpgsql.
Well, you can but its not that useful of course:
hmm, some is wrong - I remember from some older plpgsql, so CANCEL message is not catchable. Maybe I have bad memory. I have to recheck it.
=# create or replace function test_query_cancel() returns void language plpgsql as $$ begin
perform pg_sleep(1);
exception when query_canceled then raise notice 'cancel';
end; $$;
CREATE FUNCTION
=# set statement_timeout to '100ms';
SET
=# select test_query_cancel();
NOTICE: cancel
test_query_cancel
-------------------
(1 row)
=# select test_query_cancel() from generate_series(1, 100) x;
NOTICE: cancel
^CCancel request sent
NOTICE: cancel
^CCancel request sent
Now you cannot cancel this query unless you do pg_terminate_backend() or equivalent.
There is need some internal timeout - but this timeout should not be visible - any new GUC increase PostgreSQL complexity - and there is not a reason why do it.
But the GUC was added for the timeout on the sending side, not the receiving one. There is no "one value fits all" for this, but you would still want to make the effects of this as limited as possible.
I still believe so any new GUC is not necessary. If you don't like statement_timeout, we can copy the behave of CREATE DATABASE - there are few 5sec cycles (when template1 is occupated) and break.
