I've been struggling with how we're going to upgrade launchpad.net 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.
Thanks,
--
Colin Watson [cjwatson@canonical.com]