On Mon, 29 Jun 2026, 20:17 Tom Lane, <[email protected]> wrote: > Kirill Reshke <[email protected]> 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? >
