On 14 May 2013 21:04, Kevin Grittner <kgri...@ymail.com> wrote: > 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)
You're right, I think I'm having a brain-fail! I'm too used to seeing the behaviour from WHERE NOT EXISTS (SELECT 1...). Apologies for the noise. -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
