Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: PostgreSQL 10 changes in exclusion constraints - did something
 change? CASE WHEN behavior oddity
From: Mark Dilger <hornschnorter@gmail.com>
In-Reply-To: <20170604193521.ha6d5kpkdswmissm@alap3.anarazel.de>
Date: Sun, 4 Jun 2017 14:16:14 -0700
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 Regina Obe <lr@pcorp.us>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <63850C96-9570-4969-8074-7033DA4C334F@gmail.com>
References: <000001d2d5de$d8d66170$8a832450$@pcorp.us>
 <29994.1495809534@sss.pgh.pa.us> <000c01d2d654$518f2be0$f4ad83a0$@pcorp.us>
 <21809.1495994606@sss.pgh.pa.us>
 <20170603024503.yv6gxvkyx6mtc63m@alap3.anarazel.de>
 <7181.1496458380@sss.pgh.pa.us>
 <20170603031127.fqg7zjhfgbcbcank@alap3.anarazel.de>
 <4125B63A-886A-442D-9932-34F0D4F76D41@gmail.com>
 <20170604193521.ha6d5kpkdswmissm@alap3.anarazel.de>
To: Andres Freund <andres@anarazel.de>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


> On Jun 4, 2017, at 12:35 PM, Andres Freund <andres@anarazel.de> wrote:
>=20
> Hi Mark,
>=20
> 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.
>>=20
>> 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?
>=20
> 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.
>=20
> Do I understand correctly that you're essentially advocating for the <
> v10 behaviour?

No, I'm not advocating either way.  I merely wanted to know which queries
you and Tom were talking about.

>  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
>=20
>=20
>> 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:
>>=20
>> SELECT x, CASE WHEN y THEN generate_series(1,z) ELSE 5 END
>> 	FROM table_with_columns_x_and_y_and_z;
>>=20
>> 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.
>=20
> 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.
>=20
>> 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;
>=20
> 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.

Sorry, I was not clear.  What I meant to get at was that if you remove from=
 the
executor all support for SRFs inside case statements, you might foreclose t=
he option
of extending the syntax at some later date to allow aggregates over SRFs.  =
I'm
not saying that this works currently, but in principle if you allowed that =
SUM() that
I put up there, you'd get back exactly one row from it, same as you get fro=
m the
ELSE clause.  That would seem to solve the problem without going so far as
completely disallowing the SRF altogether.  The reasons for not putting a G=
ROUP BY
clause in the example are (a) I don't know where it would go, syntactically=
, and (b)
it would defeat the purpose, because once you are grouping, you again have =
the
possibility of getting more than one row.

I'm not advocating implementing this; I'm just speculating about possible f=
uture
features in which SRFs inside a case statement might be allowed.

>=20
>> Thanks, and my apologies if I am merely lacking sufficient imagination to
>> think of a proper example.
>=20
> Might be worthwhile to reread the thread about the SRF reimplementation.
>=20
> https://www.postgresql.org/message-id/20160822214023.aaxz5l4igypowyri@ala=
p3.anarazel.de

This helps, thanks!

Mark Dilger


--=20
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers