On Fri, Jul 5, 2013 at 6:10 AM, Joe Van Dyk <joe@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
