Re: PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

> On Jun 2, 2017, at 8:11 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
>
> On 2017-06-02 22:53:00 -0400, Tom Lane wrote:
>> I think you've got enough on your plate. I can take care of whatever
>> we decide to do here.
>
> Thanks.
>
>
>> Andres Freund <andres(at)anarazel(dot)de> writes:
>>>> Another possibility is to say that we've broken this situation
>>>> irretrievably and we should start throwing errors for SRFs in
>>>> places where they'd be conditionally evaluated. That's not real
>>>> nice perhaps, but it's better than the way things are right now.
>>
>>> I'd be ok with that too, but I don't really see a strong need so far.
>>
>> The argument for this way is basically that it's better to break
>> apps visibly than silently.
>
> Right, I got that.
>
>
>> The behavior for SRF-inside-CASE is
>> not going to be the same as before even if we implement the fix
>> I suggest above, and it's arguable that this new behavior is not
>> at all intuitive.
>
> Yea, I'm not a big fan of the either the pre v10 or the v10 behaviour of
> SRFs inside coalesce/case. Neither is really resonable imo - I'm not
> sure a reasonable behaviour even exists. IIRC I'd argued in the
> original SRF thread that we should just throw an error, and I think we'd
> concluded that we'd not do so for now.

I am trying to get my head around the type of query you and Tom
are discussing. When you say you are unsure a reasonable behavior
even exists, are you saying such queries have no intuitive meaning?

Can you give an example of such a query which has no intuitive
meaning? Perhaps I am not thinking about the right kind of queries.
I have been thinking about examples like:

SELECT x, CASE WHEN y THEN generate_series(1,z) ELSE 5 END
FROM table_with_columns_x_and_y_and_z;

Which to me gives 'z' output rows per table row where y is true, and
one output row per table row where y is false. That could be changed
with an aggregate function such as:

SELECT x, CASE WHEN y THEN SUM(generate_series(1,z)) ELSE 5 END
FROM table_with_columns_x_and_y;

Which to me gives one output row per table row regardless of whether y
is true.

Thanks, and my apologies if I am merely lacking sufficient imagination to
think of a proper example.

Mark Dilger