Thread: CREATE UNLOGGED MATERIALIZED VIEW
Hi! 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? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On 12/25/18 1:13 AM, Mitar wrote: > Hi! > > 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? My guess because of this: https://doxygen.postgresql.org/matview_8c.html Line 458-461 "/* * We can skip WAL-logging the insertions, unless PITR or streaming * replication is in use. We can skip the FSM in any case. */ " > > > Mitar > -- Adrian Klaver adrian.klaver@aklaver.com
Hi! That is for transient tables (NEW and OLD tables), no? Not for the main materialized view table. Mitar On Tue, Dec 25, 2018 at 10:14 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 12/25/18 1:13 AM, Mitar wrote: > > Hi! > > > > 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? > > My guess because of this: > > https://doxygen.postgresql.org/matview_8c.html > > Line 458-461 > > "/* > * We can skip WAL-logging the insertions, unless PITR or streaming > * replication is in use. We can skip the FSM in any case. > */ > " > > > > > > > Mitar > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On 12/25/18 10:16 AM, Mitar wrote: > Hi! > > That is for transient tables (NEW and OLD tables), no? Not for the > main materialized view table. Don't know. From my POV MATERIALIZED VIEWS are transient any way you look at them. They capture a state at a point in time and then the data they depend on moves on. Given that they can be refreshed at will not sure logging/not logging really matters. > > > Mitar > > On Tue, Dec 25, 2018 at 10:14 AM Adrian Klaver > <adrian.klaver@aklaver.com> wrote: >> >> On 12/25/18 1:13 AM, Mitar wrote: >>> Hi! >>> >>> 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? >> >> My guess because of this: >> >> https://doxygen.postgresql.org/matview_8c.html >> >> Line 458-461 >> >> "/* >> * We can skip WAL-logging the insertions, unless PITR or streaming >> * replication is in use. We can skip the FSM in any case. >> */ >> " >> >>> >>> >>> Mitar >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi! On Tue, Dec 25, 2018 at 10:25 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > Don't know. From my POV MATERIALIZED VIEWS are transient any way you > look at them. They capture a state at a point in time and then the data > they depend on moves on. Given that they can be refreshed at will not > sure logging/not logging really matters. I agree with you. But current default is RELPERSISTENCE_PERMANENT. So there is an option for RELPERSISTENCE_UNLOGGED. So, I think that we should or a) make RELPERSISTENCE_UNLOGGED default, b) document RELPERSISTENCE_UNLOGGED or c) remove it. I propose b). Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Mitar <mmitar@gmail.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
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@sss.pgh.pa.us> wrote: > > Mitar <mmitar@gmail.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