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

2015-09-17 16:06 GMT+02:00 Shulgin, Oleksandr <oleksandr(dot)shulgin(at)zalando(dot)de>
:

> On Thu, Sep 17, 2015 at 12:06 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.
>

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.

Regards

Pavel

>
> --
> Alex
>
>