Hi all,
here is my patch trying to implement snapshot materialized views. On
http://github.com/pbaros/postgres can be seen too (or downloaded source
code). But it is still in progress. Together with patch I enclosed sql
script that shows what can be done with MV.
Description of patch:
1) can create MV, and is created uninitialized with data
CREATE MATERIALIZED VIEW mvname AS SELECT ...
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.
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
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.
thanks for comments
Pavel Baros