It's looking at what other DBMSes do.
Notably MS SQL Server. AFAIK its CTEs are a lot like query-scoped views. They are simply updatable where possible, so you can write
WITH x AS (...)
UPDATE x SET ...
I do not know how MS SQL handles inlining and pullup/pushdown vs materialization, handles multiple evaluation costs, etc.
This is the model I would want to aim for.
It's not bad for SELECT.
But there are complexities.
- CTE terms may contain data-mutating functions people are relying on not multiply executing;
- we document that in postgres CTEs act as optimisation fences even with the standard syntax. So users rely on this as a query hint. Personally I want to relnotes this and tell people to use our OFFSET 0 hint instead, or add a NOINLINE option to our CTEs, then make pg allow inlining by default. This is a BC break, but not a big one if we restrict inlining of volatile. And since we don't have query hints (*cough*) by project policy, we can't really object to removing one can we?
- as you noted, it is hard to decide when it's worth inlining vs materializing for CTE terms referenced more than once. We should possibly start small and only inline single reference terms in the first release. We'd continue to force materializing of multiple reference terms.
That'd at least help people who use CTEs to write clearer queries not suffer for it. And it'd give us experience to help with conservatively introducing multiple reference inlining.