Re: On-demand running query plans using auto_explain and signals

On Thu, Sep 17, 2015 at 12:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

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.

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:

=# 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.

--

Alex