Re: CREATE UNLOGGED MATERIALIZED VIEW

Hi!

FYI, I started working on also adding TEMPORARY materialized views. [1]

[1] https://www.postgresql.org/message-id/CAKLmikOwqJx7J%3Dfg7VFcuk2OUjqLy%3DHZj_J5stY1x8X7%2B14g5g%40mail.gmail.com

UNLOGGED is still unsupported, but TEMPORARY materialized views do not
have the issue mentioned in the comment above because they do not exit
after the crash anyway.

Mitar

On Wed, Dec 26, 2018 at 8:35 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Mitar <mmitar(at)gmail(dot)com> writes:
> > I am looking at parser grammar rules and it looks like the following
> > is a valid query:
> > CREATE UNLOGGED MATERIALIZED VIEW
> > Any particular reason this is not documented?
>
> Because it's not supported:
>
> regression=# CREATE UNLOGGED MATERIALIZED VIEW foo AS select 1;
> ERROR: materialized views cannot be UNLOGGED
>
> Tracing down the source of that error message, we find
>
> /*
> * For now, we disallow unlogged materialized views, because it seems
> * like a bad idea for them to just go to empty after a crash. (If we
> * could mark them as unpopulated, that would be better, but that
> * requires catalog changes which crash recovery can't presently
> * handle.)
> */
> if (stmt->into->rel->relpersistence == RELPERSISTENCE_UNLOGGED)
> ereport(ERROR,
> (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("materialized views cannot be UNLOGGED")));
>
> So that's a value judgment you might or might not agree with (and even if
> you don't, it's not clear whether there are any deeper implementation
> problems that would be exposed if we just deleted this prohibition).
> But there it is, and it's why the documentation doesn't mention such a
> capability.
>
> The reason the grammar allows the possibility is likely some combination
> of (1) sharing grammar with other statements, (2) an error like the above
> is a lot more user-friendly than "syntax error" if someone tries to use
> the nonexistent feature, and (3) making provision for future enhancements.
>
> regards, tom lane

--
http://mitar.tnode.com/
https://twitter.com/mitar_m