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
