From: Mark Dilger <hornschnorter@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Regina Obe <lr@pcorp.us>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity
Date: Sun, 4 Jun 2017 14:32:24 -0700
Message-ID: <FB5BA655-95A0-4EC5-900D-2582D3E98A74@gmail.com> (raw)
In-Reply-To: <20170604211942.aiavcbagsho5owcz@alap3.anarazel.de>
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>
<63850C96-9570-4969-8074-7033DA4C334F@gmail.com>
<20170604211942.aiavcbagsho5owcz@alap3.anarazel.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
> On Jun 4, 2017, at 2:19 PM, Andres Freund <andres@anarazel.de> wrote:
>
> On 2017-06-04 14:16:14 -0700, Mark Dilger wrote:
>> 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 the option
>> of extending the syntax at some later date to allow aggregates over
>> SRFs.
>
> Seems very unlikely that we'd ever want to do that. The right way to do
> this is to simply move the SRF into the from list. Having the executor
> support arbitrary sources of tuples would just complicate and slow down
> already complicated and slow code...
>
>
>> 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 from the
>> ELSE clause. That would seem to solve the problem without going so far as
>> completely disallowing the SRF altogether.
>
> But what would the benefit be?
In my example, the aggregate function is taking a column from the table as
an argument, so the output of the aggregate function needs to be computed per row,
not just once. And if the function is expensive, or has side-effects, you might
only want it to execute for those rows where the CASE statement is true, rather
than for all of them. You may get that same behavior using lateral or some such,
I'm uncertain, but in a complicated CASE statement, it be more straightforward
to write something like:
SELECT
CASE
WHEN t.x = 'foo' THEN expensive_aggfunc1(srf1(t.y,t.z))
WHEN t.x = 'bar' THEN expensive_aggfunc2(srf2(t.y,t.z))
WHEN t.x = 'baz' THEN expensive_aggfunc3(srf3(t.y,t.z))
....
WHEN t.x = 'zzz' THEN expensive_aggfuncN(srfN(t.y,t.z))
ELSE 5
END
FROM mytable t;
Than to try to write it any other way.
I'm not advocating anything here, even though it may sound that way to you.
I'm just thinking this thing through, given that you may be committing a removal
of functionality that we want back at some later time.
Out of curiosity, how would you rewrite what I have above such that the
aggregate function is not inside the case statement, and the expensive_aggfuncs
are only called for those (t.y,t.z) that are actually appropriate?
Mark Dilger
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: hornschnorter@gmail.com, andres@anarazel.de, tgl@sss.pgh.pa.us, lr@pcorp.us
Subject: Re: PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity
In-Reply-To: <FB5BA655-95A0-4EC5-900D-2582D3E98A74@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox