Prevent folding of some WITH clauses
PostgreSQL >= 12 changes the behaviour of `WITH` clauses:
"By default, a side-effect-free `WITH` query is folded into the
primary query if it is used exactly once in the primary query's `FROM`
clause."
This will be a problem for Launchpad. Because we use a query compiler
and so have no particular need to use `WITH` clauses specifically for
readability, we only use them in two situations: common expressions used
multiple times in a query (not affected by this change) and as an
optimization fence to instruct the planner to consider particular parts
of the query first when we know that they're very likely to be more
selective. As a result, this change always makes things worse for us,
and we need to avoid it.
PostgreSQL >= 12 does provide a `MATERIALIZED` keyword that prevents
this folding, restoring the older behaviour. Unfortunately, this is not
recognized by PostgreSQL 11 and earlier. My plan A was to make this
keyword available in older versions as a no-op to ease the transition,
but my attempt to get that into PostgreSQL
(https://www.postgresql.org/message-id/20191018132130.GM16234%40riva.ucam.org)
had a distinctly mixed reception and went nowhere.
Plan B was to use `OFFSET 0` in subqueries instead, which is reportedly
possible. However, that doesn't always seem to be sufficient for our
uses: for example, in the first case I tried (the `commented_bug_ids`
and `commented_bugtask_ids` clauses in `lp.bugs.model.bugtasksearch`,
used to search for bugs commented on by a particular person), the
`OFFSET 0` approach failed to yield the correct plan. Looking at the
planner code, the only evidence I can see of this making any difference
at all is in `EXISTS` expressions.
So, this branch implements plan C: conditionally add the `MATERIALIZED`
keyword to relevant queries based on the current PostgreSQL server
version. This requires a slightly awkwardly-written `WithMaterialized`
Storm expression that takes a Storm store as an argument and then pokes
about in a couple of private attributes to extract the current server
version, but those private attributes are in practice quite stable and I
don't anticipate this causing a problem. Once we're using PostgreSQL >=
12 across the board we'll be able to drop the version detection.
