Thom Brown <t...@linux.com> wrote: > On 14 May 2013 19:51, Kevin Grittner <kgri...@ymail.com> wrote: >> In the first CF for 9.4 I plan to submit a patch to allow >> transactional REFRESH of a materialized view using differential >> update. Essentially I expect this to be the equivalent of running >> the query specified for the view and saving the results into a >> temporary table, and then doing DELETE and INSERT passes to make >> the matview match the new data. If mv is the matview and mv_temp >> is the temporary storage for the new value for its data, the logic >> would be roughly the equivalent of: >> >> BEGIN; >> LOCK mv IN SHARE ROW EXCLUSIVE MODE; >> CREATE TEMP TABLE mv_temp AS [mv query]; >> -- Create indexes here??? Capture statistics on temp table??? >> DELETE FROM mv WHERE NOT EXISTS (SELECT * FROM mv_temp >> WHERE (mv_temp.*) IS NOT DISTINCT FROM (mv.*)); >> INSERT INTO mv SELECT * FROM mv_temp WHERE NOT EXISTS >> (SELECT * FROM mv WHERE (mv.*) IS NOT DISTINCT FROM (mv_temp.*)); >> COMMIT; > > Wouldn't this either delete everything or nothing, followed by > inserting everything or nothing? WHERE NOT EXISTS wouldn't perform > any matching, just check to see whether there were matches or no > matches.
No. test=# -- Mock up the matview and the generated temp replacement in regular tables test=# -- for purposes of demonstration. test=# create table mv (id int not null primary key, val text); CREATE TABLE test=# insert into mv values (1, 'one'), (2, 'two'), (3, null), (4, 'four'); INSERT 0 4 test=# create temp table mv_temp as select * from mv; SELECT 4 test=# update mv_temp set val = null where id = 4; UPDATE 1 test=# update mv_temp set val = 'zwei' where id = 2; UPDATE 1 test=# delete from mv_temp where id = 1; DELETE 1 test=# insert into mv_temp values (5, 'five'); INSERT 0 1 test=# -- Show both. test=# select * from mv order by id; id | val ----+------ 1 | one 2 | two 3 | 4 | four (4 rows) test=# select * from mv_temp order by id; id | val ----+------ 2 | zwei 3 | 4 | 5 | five (4 rows) test=# -- Perform the differential update's delete. test=# delete from mv where not exists (select * from mv_temp test(# where (mv_temp.*) is not distinct from (mv.*)); DELETE 3 test=# -- Show both. test=# select * from mv order by id; id | val ----+----- 3 | (1 row) test=# select * from mv_temp order by id; id | val ----+------ 2 | zwei 3 | 4 | 5 | five (4 rows) test=# -- Perform the differential update's insert. test=# insert into mv select * from mv_temp where not exists test-# (select * from mv where (mv.*) is not distinct from (mv_temp.*)); INSERT 0 3 test=# -- Show both. test=# select * from mv order by id; id | val ----+------ 2 | zwei 3 | 4 | 5 | five (4 rows) test=# select * from mv_temp order by id; id | val ----+------ 2 | zwei 3 | 4 | 5 | five (4 rows) -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
