Re: Making CASE error handling less surprising

čt 23. 7. 2020 v 21:56 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
> čt 23. 7. 2020 v 21:43 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
>
>> Andres Freund <andres(at)anarazel(dot)de> writes:
>> > On 2020-07-23 18:50:32 +0100, Dagfinn Ilmari Mannsåker wrote:
>> >> Would it be feasible to set up an exception handler when constant-
>> >> folding cases that might not be reached, and leave the expression
>> >> unfolded only if an error was thrown, or does that have too much
>> >> overhead to be worthwhile?
>>
>> > That'd require using a subtransaction for expression
>> > simplification. That'd be way too high overhead.
>>
>> That's my opinion as well. It'd be a subtransaction for *each*
>> operator/function call we need to simplify, which seems completely
>> disastrous.
>>
>> > Given how often we've had a need to call functions while handling
>> > errors, I do wonder if it'd be worthwhile and feasible to mark functions
>> > as being safe to call without subtransactions, or mark them as not
>> > erroring out (e.g. comparators would usually be safe).
>>
>> Yeah. I was wondering whether the existing "leakproof" marking would
>> be adequate for this purpose. It's a little stronger than what we
>> need, but the pain-in-the-rear factor for adding YA function property
>> is high enough that I'm inclined to just use it anyway.
>>
>> We do have to assume that "leakproof" includes "cannot throw any
>> input-dependent error", but it seems to me that that's true.
>>
>
> I am afraid of a performance impact.
>
> lot of people expects constant folding everywhere now and I can imagine
> query like
>
> SELECT CASE col1 WHEN 1 THEN upper('hello') ELSE upper('bye') END FROM ...
>
> Now, it is optimized well, but with the proposed patch, this query can be
> slow.
>
> We should introduce planner safe functions for some usual functions, or
> maybe better explain the behaviour, the costs, and benefits. I don't think
> this issue is too common.
>

what about different access. We can introduce function

create or replace function volatile_expr(anyelement) returns anyelement as
$$ begin return $1; end $$ language plpgsql;

and this can be used as a constant folding optimization fence.

select case col when 1 then volatile_expr(1/$1) else $1 end;

I don't think so people have a problem with this behaviour - the problem is
unexpected behaviour change between major releases without really
illustrative explanation in documentation.

Regards

Pavel

> Regards
>
> Pavel
>
>
>
>> regards, tom lane
>>
>>
>>