Re: PostgreSQL select-only CTE removal is too aggressive?

Kirill Reshke <reshkekirill(at)gmail(dot)com> writes:
> I have been thinking for a while about this optimization. The thing is,
> there is currently no way of forcing this CTE evaluation other than
> referencing it from other query parts. I think this is little clumsy. What
> if we use MATERIALIZE here for this purpose? So,
> WITH d as MATERIALIZED (select f()) select 1 ;
> will force evaluation? Does this strike you as good idea?

No. What is the point?

Also, this would be far from a one-liner change. Stopping the
planner from throwing away unreferenced CTEs wouldn't cause the
executor to run them. Currently the executor only pays attention
to running ModifyTable nodes to completion.

The short answer here is that this feature has behaved like this since
we implemented it, in 2008 (cf commit 44d5be0e5). AFAIR you are the
first to complain about it. You should seriously consider the
possibility that it's you that are mistaken about the value of doing
things differently. Even if there is some value, I think that
backwards compatibility would argue against changing the semantics.

regards, tom lane