pá 24. 7. 2020 v 18:49 odesílatel Andres Freund <and...@anarazel.de> napsal:
> Hi, > > On 2020-07-24 12:31:05 -0400, Robert Haas wrote: > > On Thu, Jul 23, 2020 at 12:57 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > > Every so often we get a complaint like [1] about how a CASE should have > > > prevented a run-time error and didn't, because constant-folding tried > > > to evaluate a subexpression that would not have been entered at > run-time. > > > > Yes, I've heard such complaints from other sources as well. > > > > > It struck me that it would not be hard to improve this situation a > great > > > deal. If, within a CASE subexpression that isn't certain to be > executed > > > at runtime, we refuse to pre-evaluate *any* function (essentially, > treat > > > them all as volatile), then we should largely get the semantics that > > > users expect. There's some potential for query slowdown if a CASE > > > contains a constant subexpression that we formerly reduced at plan time > > > and now do not, but that doesn't seem to me to be a very big deal. > > > > Like Pavel, and I think implicitly Dagfinn and Andres, I'm not sure I > > believe this. Pavel's example is a good one. The leakproof exception > > helps, but it doesn't cover everything. Users I've encountered throw > > things like date_trunc() and lpad() into SQL code and expect them to > > behave (from a performance point of view) like constants, but they > > also expect 1/0 not to get evaluated too early when e.g. CASE is used. > > It's difficult to meet both sets of expectations at the same time and > > we're probably never going to have a perfect solution, but I think > > you're minimizing the concern too much here. > > Wouldn't the rule that I proposed earlier, namely that sub-expressions > that involve only "proper" constants continue to get evaluated even > within CASE, largely address that? > It doesn't solve a possible performance problem with one shot (EXECUTE stmt plpgsql) queries, or with parameterized queries > > > I don't think I believe this either. I don't think an average user is > > going to expect <expression> to behave differently from (SELECT > > <expression>). This one actually bothers me more than the previous > > one. How would we even document it? Sometimes things get inlined, > > sometimes they don't. Sometimes subqueries get pulled up, sometimes > > not. The current behavior isn't great, but at least it handles these > > cases consistently. Getting the easy cases "right" while making the > > behavior in more complex cases harder to understand is not necessarily > > a win. > > Well, if we formalize the desired behaviour it's probably a lot easier > to work towards implementing it in additional cases (like > subselects). It doesn't seem to hard to keep track of whether a specific > subquery can be evaluate constants in a certain way, if that's what we > need. > > Greetings, > > Andres Freund > > >
