Re: [HACKERS] Snapshot Materialized Views - GSoC

2010-05-26 Thread Peter Eisentraut
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.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Snapshot Materialized Views - GSoC

2010-05-21 Thread Robert Haas
2010/5/20 Pavel baro...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Snapshot Materialized Views - GSoC

2010-05-21 Thread Massa, Harald Armin
Pavel,

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


please do not fortget the:

create or replace MATERIALIZED VIEW

option.

And also the

DROP if exists

for the drop-command


Best wishes

Harald


-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
Using PostgreSQL is mostly about sleeping well at night.


Re: [HACKERS] Snapshot Materialized Views - GSoC

2010-05-21 Thread Tom Lane
Massa, Harald Armin c...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Snapshot Materialized Views - GSoC

2010-05-21 Thread Florian Pflug
On May 21, 2010, at 15:59 , Robert Haas wrote:
 2010/5/20 Pavel baro...@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's just an empty table with a rewrite 
rule attached. I think relkind is mostly there to make pg_dump's and the 
information schema'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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Snapshot Materialized Views - GSoC

2010-05-20 Thread Jaime Casanova
2010/5/20 Pavel baro...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers