Re: Backport "WITH ... AS MATERIALIZED" syntax to <12?

Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
> +0.5

> In general, I'm not opposed to accepting and ignoring the MATERIALIZED
> syntax (assuming we'd only accept AS MATERIALIZED, but not the negative
> variant).

FWIW, I'm +0.1 or thereabouts. I'd vote -1 if the patch required
introducing a new lexer keyword (even an unreserved one); but it
doesn't. So it's hard to argue that there's much downside.

(If we do this, I wonder if we should make the back branches parse
NOT MATERIALIZED as well, and then throw a "not implemented" error
rather than the unhelpful syntax error you'd get today.)

(Also, if we do this, I think we should patch all supported branches.
The OP's proposal to patch back to 10 has no foundation that I can see.)

> FWIW I'm not sure the "we don't want to upgrade application code at the
> same time as the database" is really tenable. I don't think we really
> promise that anywhere, and adding the AS MATERIALIZED seems quite
> mechanical. I think we could find cases where we caused worse breaks
> between major versions.

That's certainly true, which is why I'm only lukewarm about the proposal.

> One disadvantage is that this will increase confusion for users, who'll
> get used to the behavior on 12, and then they'll get confused on older
> releases (e.g. if you don't specify AS MATERIALIZED you'd expect the CTE
> to get inlined, but that won't happen).

I'm less concerned about that aspect than about the aspect of (for
instance) 11.6 and up allowing a syntax that 11.0-11.5 don't. People
are likely to write code relying on this and then be surprised when
it doesn't work on a slightly older server. Still, is that so much
different from cases where we fix a bug that prevented some statement
from working?

regards, tom lane