- GSoC - snapshot materialized view (work-in-progress)patch
in [PgSql]
|
Prev: [HACKERS] CommitFest 2010-07 Plans and Call for Reviewers
Next: - GSoC - snapshot materialized view (work-in-progress) patch
From: =?ISO-8859-2?Q?Pavel_Baro=B9?= on 12 Jul 2010 10:16 Dne 9.7.2010 21:33, Robert Haas napsal(a): > 2010/7/8 Pavel Baro�<baros.p(a)seznam.cz>: >> Description of patch: >> 1) can create MV, and is created uninitialized with data >> CREATE MATERIALIZED VIEW mvname AS SELECT ... > > This doesn't seem acceptable. It should populate it on creation. > Yes, it would be better, in addition, true is, this behavior will be required if is expected to implement incremental MV in the close future. >> 2) can refresh MV >> ALTER MATERIALIZED VIEW mvname REFRESH >> >> 3) MV cannot be modified by DML commands (INSERT, UPDATE and DELETE >> are not >> permitted) >> >> 4) index can be created and used with MV >> >> 5) pg_dump is repaired, in previous patch dump threw error, now dont, >> but it >> is sort of dummy, I want to reach state, where refreshing command >> will be >> posed after all COPY statements (when all data are in tables). In >> this patch >> REFRESH command is right behind CREATE MV command. > > Hmm... ISTM that you probably need some kind of dependency stuff in > here to make the materialized view get created after the tables it > depends on have been populated with data. It needs to work with > parallel restore, too. I'm not sure exactly how the dependency stuff > in pg_dump works, though. > never mind in case MV will be populated on creation. > A subtle point here is that if you dump and restore a database > containing a materialized view, the new database might not be quite > the same as the old one, because the materialized view might have been > out of date before, and when you recreate it, it'll get refreshed. > I'm not sure there's much we can/should do about that, though. > yes, it is interesting, of course, there can be real-life example, where population on creating is needed and is not, and I'm thinking of solution similar to Oracle or DB2. Add some option to creating MV, that enable/disable population on creating: http://www.ibm.com/developerworks/data/library/techarticle/dm-0708khatri/ Oracle: CREATE MATERIALIZED VIEW mvname [ BUILD [IMMEDIATE | DEFERRED] ] AS SELECT .. DB2: CREATE TABLE mvname AS SELECT ... [ INITIALLY DEFERRED | IMMEDIATE ] >> 6) psql works too, new command \dm[S+] was added to the list >> \d[S+] [PATTERN] - lists all db objects like tables, view, >> materialized >> view and sequences >> \dm[S+] [PATTERN] - lists all materialized views >> I also noticed I forgot handle options \dp and \dpp, this should be OK in next version of patch. >> 7) there are some docs too, but I guess it is not enough, at least my >> english will need to correct > > If we're going to treat materialized views as a separate object type, > you probably need to break out the docs for CREATE MATERIALIZED VIEW, > ALTER MATERIALIZED VIEW, and DROP MATERIALIZED VIEW into their own > pages, rather than having then mixed up with corresponding pages for > regular views. > Yeah, that was problem I just solved like that here, but I confess this would be better. >> In progress: >> - regression tests >> - behavior of various ALTER commands, ie SET STATISTIC, CLUSTER ON, >> ENABLE/DISABLE RULE, etc. > > This isn't right: > > rhaas=# create view v as select * from t; > CREATE VIEW > rhaas=# alter view v refresh; > ERROR: unrecognized alter table type: 41 > I know, cases like that will be more than that. Thats why I work on good tests now. > Please add your patch here, so that it will be reviewed during the > about-to-begin CommitFest. > > https://commitfest.postgresql.org/action/commitfest_view/open > OK, but will you help me with that form? Do you think I can fill it like that? I'm not sure about few fields .. Name: Snapshot materialized views CommitFest Topic: [ Miscellaneous | SQL Features ] ??? Patch Status: Needs review Author: me Reviewers: You? Commiters: who? and I quess fields 'Date Closed' and 'Message-ID for Original Patch' will be filled later. thanks a lot Pavel Baros |