Kirill Reshke <reshkekirill@gmail.com> writes:
> I don't find where this behaviour is actually explicitly documented.
> For data-modifying CTEs we have this[1]:
> Data-modifying statements in WITH are executed exactly once, and
> always to completion, independently of whether the primary query reads
> all (or indeed any) of their output. Notice that this is different
> from the rule for SELECT in WITH: as stated in the previous section,
> execution of a SELECT is carried only as far as the primary query
> demands its output.
I don't see how this is not a direct, obvious consequence of that
rule. The primary query demands none of the unreferenced CTE's
output, therefore it is not executed at all.
The referenced text in the "previous section" is
This works because PostgreSQL's implementation
evaluates only as many rows of a WITH query as are actually
fetched by the parent query.
which is the same thing in slightly different words.
regards, tom lane
Thank you for clarifications.
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?
