Thread: Snapshot Materialized Views - GSoC

First of all, I really appreciate you gave me change to participate on 
GSoC. It's great chance for me.

For this summer I have plan to make patch inplementing snapshot 
materialized views (MV). I believe it will not be end of effort to 
implement more of MV. But I / we need discuss MV syntax and exact 
behaviour so I have some questions about that for all of you:

a) relkind for materialized view in pg_class?   - I'm voting for char 'm' quite obvious why, but not sure about alias:
  1 - RELKIND_MVIEW     2 - RELKIND_MATVIEW        or any other ideas?
 

b) create MV syntax?   - CREATE MATERIALIZED VIEW mvname AS ..., I think it is quite
obvious to do so, but I had to ask

c) refresh command syntax?     1 - ALTER MATERIALIZED VIEW mvname REFRESH     or     2 - REFRESH MATERIALIZED VIEW
mvname

d) what to do when someone use INSERT, UPDATE or DELETE against MV?   1 - raise error? - I prefer this option   2 - let
commandschange MV? (no chance to let changes propagate to
 
source tables, not for this summer :)   if pg lets user to DML against MV, I expect that triggers should 
work too

e) what to do when someone drop table or column?  - it behave like it was a classic view. Fire error and hint  -
CASCADEoption will remove MV
 

ERROR:  cannot drop table adresa because other objects depend on it
DETAIL:  materialized view adresa_mv depends on table adresa
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

2010/5/20 Pavel <baros.p@seznam.cz>:
>
> d) what to do when someone use INSERT, UPDATE or DELETE against MV?
>   1 - raise error? - I prefer this option

+1, FWIW

>   2 - let commands change MV? (no chance to let changes propagate to
> source tables, not for this summer :)
>   if pg lets user to DML against MV, I expect that triggers should work too
>

no, if you don't propagate then you don't have a view of the tables
the MV comes from...
error if you'll not implement propagation now


--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL

2010/5/20 Pavel <baros.p@seznam.cz>:
> For this summer I have plan to make patch inplementing snapshot materialized
> views (MV). I believe it will not be end of effort to implement more of MV.
> But I / we need discuss MV syntax and exact behaviour so I have some
> questions about that for all of you:
>
> a) relkind for materialized view in pg_class?
>   - I'm voting for char 'm' quite obvious why, but not sure about alias:
>     1 - RELKIND_MVIEW
>     2 - RELKIND_MATVIEW
>        or any other ideas?

I think the prior question is whether we need to create a new relkind
at all.  I'm prepared to believe that the answer is yes, but I'd like
to see a clear justification of why we can't use either 'v' or 'r'.
It seems to me that a materialized view is a lot like a regular old
table with a special rewrite rule attached to it somewhere.

> b) create MV syntax?
>   - CREATE MATERIALIZED VIEW mvname AS ..., I think it is quite
> obvious to do so, but I had to ask

I think that's OK.

> c) refresh command syntax?
>     1 - ALTER MATERIALIZED VIEW mvname REFRESH
>     or
>     2 - REFRESH MATERIALIZED VIEW mvname

1.

> d) what to do when someone use INSERT, UPDATE or DELETE against MV?
>   1 - raise error? - I prefer this option
>   2 - let commands change MV? (no chance to let changes propagate to
> source tables, not for this summer :)
>   if pg lets user to DML against MV, I expect that triggers should work too

1.

> e) what to do when someone drop table or column?
>  - it behave like it was a classic view. Fire error and hint
>  - CASCADE option will remove MV

Agree.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

"Massa, Harald Armin" <chef@ghum.de> writes:
> please do not fortget the:
> create or replace MATERIALIZED VIEW
> option.

Please do.  For something as complex as a table or view, CREATE OR
REPLACE is a lot more complicated than it is for simple objects like
functions.  (See flamewar just a couple weeks ago about C.O.R. vs
CREATE IF NOT EXISTS for tables.)  Putting this on the to-do list
for the GSOC project will just about guarantee failure.  It's most
likely too large a task for a GSOC project already...
        regards, tom lane

On May 21, 2010, at 15:59 , Robert Haas wrote:
> 2010/5/20 Pavel <baros.p@seznam.cz>:
>> For this summer I have plan to make patch inplementing snapshot materialized
>> views (MV). I believe it will not be end of effort to implement more of MV.
>> But I / we need discuss MV syntax and exact behaviour so I have some
>> questions about that for all of you:
>>
>> a) relkind for materialized view in pg_class?
>>   - I'm voting for char 'm' quite obvious why, but not sure about alias:
>>     1 - RELKIND_MVIEW
>>     2 - RELKIND_MATVIEW
>>        or any other ideas?
>
> I think the prior question is whether we need to create a new relkind
> at all.  I'm prepared to believe that the answer is yes, but I'd like
> to see a clear justification of why we can't use either 'v' or 'r'.
> It seems to me that a materialized view is a lot like a regular old
> table with a special rewrite rule attached to it somewhere.

I guess the justification is that with the same argument you could argue that a view should have relkind 'r', since
it'sjust an empty table with a rewrite rule attached. I think relkind is mostly there to make pg_dump's and the
informationschema's job easier - without it, distinguishing tables with ON SELECT rules from views seem rather
AI-complete.The same holds for materialized views vs. tables and materialized views vs. views. 

best regards,
Florian Pflug

On fre, 2010-05-21 at 16:31 +0200, Florian Pflug wrote:
> I guess the justification is that with the same argument you could
> argue that a view should have relkind 'r', since it's just an empty
> table with a rewrite rule attached.

It used to be that way, but now a view doesn't have an empty table
attached to it, but no table at all.  Hence the different relkind.