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

On Sat, Oct 19, 2019 at 11:56:56AM +0200, Andres Freund wrote:
>Hi,
>
>On October 19, 2019 6:01:04 AM GMT+02:00, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
>>>>>>> "Michael" == Michael Paquier <michael(at)paquier(dot)xyz> writes:
>>
>> > On Fri, Oct 18, 2019 at 02:21:30PM +0100, Colin Watson wrote:
>> >> However, an alternative would be to backport the new syntax to some
>> >> earlier versions. "WITH ... AS MATERIALIZED" can easily just be
>> >> synonymous with "WITH ... AS" in versions prior to 12; there's no
>> >> need to support "NOT MATERIALIZED" since that's explicitly
>> >> requesting the new query-folding feature that only exists in 12.
>> >> Would something like the attached patch against REL_11_STABLE be
>> >> acceptable? I'd like to backpatch it at least as far as PostgreSQL
>> >> 10.
>>
>> Michael> I am afraid that new features don't gain a backpatch. This is
>>Michael> a project policy. Back-branches should just include bug fixes.
>>
>>I do think an argument can be made for making an exception in this
>>particular case. This wouldn't be backpatching a feature, just
>>accepting
>>and ignoring some of the new syntax to make upgrading easier.
>
>+1
>

+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 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.

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).

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services