Lists: | pgsql-hackers |
---|
From: | Pavel <baros(dot)p(at)seznam(dot)cz> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Snapshot Materialized Views - GSoC |
Date: | 2010-05-20 16:32:17 |
Message-ID: | 4BF56411.20601@seznam.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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 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
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
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.
From: | Jaime Casanova <jaime(at)2ndquadrant(dot)com> |
---|---|
To: | Pavel <baros(dot)p(at)seznam(dot)cz> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Snapshot Materialized Views - GSoC |
Date: | 2010-05-20 17:35:22 |
Message-ID: | AANLkTikCPa3CbOemUO4LiYflLwcuwxPcuVpOSuE9ZBGF@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2010/5/20 Pavel <baros(dot)p(at)seznam(dot)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
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Pavel <baros(dot)p(at)seznam(dot)cz> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Snapshot Materialized Views - GSoC |
Date: | 2010-05-21 13:59:05 |
Message-ID: | AANLkTikmjRekbYzSEyws3JDKIBwrb8wgcAgXQHlkj1t2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2010/5/20 Pavel <baros(dot)p(at)seznam(dot)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
From: | "Massa, Harald Armin" <chef(at)ghum(dot)de> |
---|---|
To: | Pavel <baros(dot)p(at)seznam(dot)cz> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Snapshot Materialized Views - GSoC |
Date: | 2010-05-21 14:02:48 |
Message-ID: | AANLkTingxr2oMurzB2PM3NVjUohsDYu_NkcyDwEuksIo@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Massa, Harald Armin" <chef(at)ghum(dot)de> |
Cc: | Pavel <baros(dot)p(at)seznam(dot)cz>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Snapshot Materialized Views - GSoC |
Date: | 2010-05-21 14:13:11 |
Message-ID: | 19154.1274451191@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
"Massa, Harald Armin" <chef(at)ghum(dot)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
From: | Florian Pflug <fgp(at)phlo(dot)org> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Pavel <baros(dot)p(at)seznam(dot)cz>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Snapshot Materialized Views - GSoC |
Date: | 2010-05-21 14:31:21 |
Message-ID: | 26A82471-FE12-42B9-B4E8-3BDD57849CD2@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On May 21, 2010, at 15:59 , Robert Haas wrote:
> 2010/5/20 Pavel <baros(dot)p(at)seznam(dot)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
From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | Florian Pflug <fgp(at)phlo(dot)org> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Pavel <baros(dot)p(at)seznam(dot)cz>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Snapshot Materialized Views - GSoC |
Date: | 2010-05-26 09:57:40 |
Message-ID: | 1274867860.4843.2.camel@fsopti579.F-Secure.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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.