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

On Mon, 29 Jun 2026 at 13:52, Kirill Reshke <reshkekirill(at)gmail(dot)com> wrote:
>
> Hi!
>
> PostgreSQL planner currently replaces not-referenced CTE with dummy
> relations here [0]
>
> But it seems to be too aggressive for subqueries with side-effects:
>
> ```
> create table z(i int, j int, k int);
> create or replace function f() returns void as $$ insert into z
> values(1,1,1) $$ language sql;
> reshke=# explain with d as (select f(1)) select 1;
> QUERY PLAN
> ------------------------------------------
> Result (cost=0.00..0.01 rows=1 width=4)
> (1 row)
>
> ```
>
> 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.
> ```
>
> Maybe we should add here a notice, that execution of an unreferenced
> SELECT CTE, even one containing a data-modifying function, is not
> guaranteed?
>
> [0] https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/optimizer/plan/subselect.c?id=26255a320733de2d91a30bd6ae529dd01e7f3409#n905
> [1] https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING
>
>
> --
> Best regards,
> Kirill Reshke

Also, PFA simple patch that fixes exactly my case and maybe breaks
many others. WDYT?

```
reshke(at)yezzey-cbdb-bench:~/pg$ git diff src/backend/optimizer/plan/subselect.c
diff --git a/src/backend/optimizer/plan/subselect.c
b/src/backend/optimizer/plan/subselect.c
index 6aa8971c95d..967fd4e7bf8 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -904,7 +904,7 @@ SS_process_ctes(PlannerInfo *root)
/*
* Ignore SELECT CTEs that are not actually referenced anywhere.
*/
- if (cte->cterefcount == 0 && cmdType == CMD_SELECT)
+ if (cte->cterefcount == 0 && cmdType == CMD_SELECT &&
!contain_volatile_functions(cte->ctequery))
{
/* Make a dummy entry in cte_plan_ids */
root->cte_plan_ids =
lappend_int(root->cte_plan_ids, -1);
```

--
Best regards,
Kirill Reshke