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

From: Colin Watson <cjwatson(at)canonical(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Backport "WITH ... AS MATERIALIZED" syntax to <12?
Date: 2019-10-18 13:21:30
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

I've been struggling with how we're going to upgrade to
PostgreSQL 12 or newer (we're currently on 10). We're one of those
applications that deliberately uses CTEs as optimization fences in a few
difficult places. The provision of the MATERIALIZED keyword in 12 is
great, but the fact that it doesn't exist in earlier versions is
awkward. We certainly don't want to upgrade our application code at the
same time as upgrading the database, and dealing with performance
degradation between the database upgrade and the application upgrade
doesn't seem great either (not to mention that it would be hard to
coordinate). That leaves us with hacking our query compiler to add the
MATERIALIZED keyword only if it's running on 12 or newer, which would be
possible but is pretty cumbersome.

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.

This compiles and passes regression tests.


Colin Watson [cjwatson(at)canonical(dot)com]

Attachment Content-Type Size
0001-Backport-WITH-.-AS-MATERIALIZED-syntax.patch text/x-diff 7.7 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2019-10-18 13:22:00 Re: UPSERT on view does not find constraint by name
Previous Message Stephen Frost 2019-10-18 13:03:31 Re: Proposal: Make use of C99 designated initialisers for nulls/values arrays