Re: WITH NOT MATERIALIZED and DML CTEs

Elvis Pranskevichus <elprans(at)gmail(dot)com> writes:
> On Monday, June 3, 2019 11:50:15 A.M. EDT Andres Freund wrote:
>>> This seems like an omission to me. Ideally, the presence of an
>>> explicit "NOT MATERIALIZED" clause on a data-modifying CTE should
>>> disable the "run to completion" logic.

>> I don't see us ever doing that. The result of minor costing and other
>> planner changes would yield different updated data. That'll just
>> create endless bug reports.

> 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. I concur with
Andres that turning off run-to-completion for DMLs would be disastrous.
For just one obvious point, what about firing AFTER triggers?

It's already the case that the planner will silently ignore NOT
MATERIALIZED for other cases where it can't inline the CTE for semantic
or implementation reasons -- see comments in SS_process_ctes(). I see
no good reason to treat the DML exception much differently from other
exceptions, such as presence of volatile functions or recursion.

regards, tom lane