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

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.

> 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.

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.

> 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
>
> 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.

> 8) some ALTER TABLE commands works, ie. RENAME TO, OWNER TO, SET SCHEMA, SET
> TABLESPACE
>
> 9) MV and columns can be commented
>
> 10) also some functions behave as expected, but if you know about some I did
> not mention and could fail when used with MV, I appreciate your hints
>     pg_get_viewdef()
>     pg_get_ruledef()
>     pg_relation_filenode()
>     pg_relation_filepath()
>     pg_table_size()
>
>
> 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

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

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