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

Hi Mark,

On 2017-06-04 11:55:03 -0700, Mark Dilger wrote:
> > 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?

I'm not saying that there aren't some cases where it's intuitive, but
there's definitely lots that don't have intuitive meaning. Especially
when there are multiple SRFs in the same targetlist.

Do I understand correctly that you're essentially advocating for the <
v10 behaviour? It'd be nontrivial to implement that, without loosing
the significant benefits (Significantly slower, higher code complexity,
weird behaviour around multiple SRFs) gained by removing the previous
implementation. I'd really like to see some examples of when all of
this is useful - I've yet to see a realistic one that's not just as
easily written differently

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

Try any query that has one SRF outside of the CASE, and one inside. In
the old behaviour that'll make the total number of rows returned nearly
undeterministic because of the least-common-multiple behaviour.

> 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;

That query doesn't work. First off, aggregates don't take set arguments
(neither in old nor new releases), secondly aggregates are evaluated
independently of CASE/COALESCE statements, thirdly you're missing group
bys. Those all are independent of the v10 changes.

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

Might be worthwhile to reread the thread about the SRF reimplementation.

https://www.postgresql.org/message-id/20160822214023.aaxz5l4igypowyri@alap3.anarazel.de

- Andres