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
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
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
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]/messages/by-id/CAKLmikOwqJx7J=fg7VFcuk2OUjqLy=HZj_J5stY1x8X7+14g5g@mail.gmail.com
[1]: /messages/by-id/CAKLmikOwqJx7J=fg7VFcuk2OUjqLy=HZj_J5stY1x8X7+14g5g@mail.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 UNLOGGEDTracing 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