Re: Efficiency of materialized views refresh in 9.3

On Fri, Jul 5, 2013 at 6:10 AM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
> Hi,
>
> Is refreshing a materialized view in 9.3 basically:
>
> delete from mat_view;
> insert into mat_view select * from base_view;
Nope. Here is some documentation:
http://www.postgresql.org/docs/devel/static/rules-materializedviews.html

And an example:
postgres=# create table aa (a int);
CREATE TABLE
postgres=# insert into aa values (1),(2);
INSERT 0 2
postgres=# create materialized view aam as select * from aa;
SELECT 2
postgres=# select * from aam;
a
---
1
2
(2 rows)
postgres=# insert into aa values (3);
INSERT 0 1
postgres=# select * from aam;
a
---
1
2
(2 rows)
postgres=# refresh materialized view aam;
REFRESH MATERIALIZED VIEW
postgres=# select * from aam;
a
---
1
2
3
(3 rows)

The REFRESH step takes an exclusive lock on the materialized view
during the time of its operation as far as I recall.

> Or is it more efficient? If no rows have changed, will new tuples be written
> on a refresh?
Materialized views in 9.3 have no support DML (which would be used for
incremental updates? Someone correct me here if I'm wrong...).
postgres=# delete from aam where a = 2;
ERROR: 42809: cannot change materialized view "aam"
LOCATION: CheckValidResultRel, execMain.c:1005

Thanks,
--
Michael