From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Dilger <hornschnorter@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
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: Wed, 07 Jun 2017 14:57:22 -0400
Message-ID: <13450.1496861842@sss.pgh.pa.us> (raw)
In-Reply-To: <FB5BA655-95A0-4EC5-900D-2582D3E98A74@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>
<63850C96-9570-4969-8074-7033DA4C334F@gmail.com>
<20170604211942.aiavcbagsho5owcz@alap3.anarazel.de>
<FB5BA655-95A0-4EC5-900D-2582D3E98A74@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Mark Dilger <hornschnorter@gmail.com> writes:
>> On Jun 4, 2017, at 2:19 PM, Andres Freund <andres@anarazel.de> wrote:
>> 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...
> 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;
I think the correct way to do that already exists, namely to use a
correlated sub-select to wrap each SRF+aggregate:
...
WHEN t.x = 'foo' THEN (SELECT expensive_aggfunc1(s) FROM srf1(t.y,t.z) s)
...
I don't really feel a need to invent some other notation for that.
After chewing on this for awhile, I'm starting to come to the conclusion
that we'd be best off to throw an error for SRF-inside-CASE (or
COALESCE). Mark is correct that the simplest case of
SELECT x, CASE WHEN y THEN generate_series(1,z) ELSE 5 END
FROM table_with_columns_x_and_y_and_z;
behaves just intuitively enough that people might be using it. The new
implementation method cannot reasonably duplicate the old semantics for
that, which means that if we let it stand as-is we will be silently
breaking queries, even if we fix up some of the weirder corner cases like
what happens when the CASE can be const-simplified. So I think we'd be
better off to make this throw an error, and force any affected users to
rewrite in a way that will work in both v10 and older releases.
As to *how* to throw an error, I think it should be possible to teach
parse analysis to detect such cases, with something like the
ParseExprKind mechanism that could be checked to see if we're inside
a subexpression that restricts what's allowed. There are some other
checks like no-nested-aggregates that perhaps could be folded in as
well. Checking at parse analysis ought to be sufficient because
rule rewriting could not introduce such a case where it wasn't before,
and planner subquery flattening won't introduce one either because we
don't flatten subqueries with SRFs in their tlists.
If people are on board with throwing an error, I'll go see about
writing a patch.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, hornschnorter@gmail.com, andres@anarazel.de, lr@pcorp.us
Subject: Re: PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity
In-Reply-To: <13450.1496861842@sss.pgh.pa.us>
* 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