Re: WITH NOT MATERIALIZED and DML CTEs

Elvis Pranskevichus <elprans(at)gmail(dot)com> writes:
> On Monday, June 3, 2019 12:09:46 P.M. EDT Tom Lane wrote:
>>> I understand why the rule exists in the first place, but I think
>>> that an explicit opt-in signals the assumption of responsibility
>>> and opens the possibility of using this in a well-defined
>>> evaluation context, such as CASE WHEN.

>> TBH, if you think it's well-defined, you're wrong.

> The documentation seems to strongly suggest otherwise:

> "When it is essential to force evaluation order, a CASE construct (see
> Section 9.17) can be used. ... CASE construct used in this fashion will
> defeat optimization attempts"

> Are there cases where this is not true outside of the documented
> exceptions (i.e. immutable early-eval and aggregates)?

CASE is a scalar-expression construct. It's got little to do with
the timing of scan/join operations such as row fetches. We offer
users essentially no control over when those happen ... other than
the guarantees about CTE materialization, which are exactly what
you say you want to give up.

regards, tom lane