Re: Differential (transactional) REFRESH

Thom Brown <thom(at)linux(dot)com> wrote:
> On 14 May 2013 19:51, Kevin Grittner <kgrittn(at)ymail(dot)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