Re: Inconsistency with EXPLAIN ANALYZE CREATE MATERIALIZED VIEW

On Tue, 2024-08-06 at 14:36 -0400, Tom Lane wrote:
> I'm not really sure I see the point of this, if it doesn't "just
> work"
> with all variants of C.M.V.  It's not like you can't easily EXPLAIN
> the view's SELECT.
>
> If REFRESH M. V. does something different than CREATE, there would
> certainly be value in being able to EXPLAIN what that does --- but
> that still isn't an argument for allowing EXPLAIN CREATE MATERIALIZED
> VIEW.

We already allow EXPLAIN ANALYZE CREATE MATERIALIZED VIEW in all
supported versions.

That seems strange and it surprised me, but the parser structure is
shared between SELECT ... INTO and CREATE MATERIALIZED VIEW, so I
suppose it was supported out of convenience.

The problem is that the implentation is split between the EXPLAIN
ANALYZE path and the non-EXPLAIN path. The long-ago commit f3ab5d4696
missed the EXPLAIN path. NB: I do not believe this is a security
concern, but it does create the inconsistency described in the email
starting this thread.

Options:

1. Do nothing on the grounds that EXPLAIN ANALYZE CREATE MATERIALIZED
VIEW is not common enough to worry about, and the consequences of the
inconsistency are not bad enough.

2. Refactor some more to make EXPLAIN ANALYZE CREATE MATERIALIZED VIEW
share the query part of the code path with REFRESH so that it benefits
from the SECURITY_RESTRICTED_OPERATION and RestrictSearchPath().

3. Do #2 but also make it work for REFRESH, but not CONCURRENTLY.

4. Do #3 but also make it work for REFRESH ... CONCURRENTLY and provide
new information that's not available by only explaining the query.

And also figure out if any of this should be back-patched.

Regards,
Jeff Davis