Re: Differential (transactional) REFRESH

On 14 May 2013 21:04, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> 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)

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