Re: - GSoC - snapshot materialized view (work-in-progress) patch

Dne 9.7.2010 21:33, Robert Haas napsal(a):
> 2010/7/8 Pavel Baroš<baros(dot)p(at)seznam(dot)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