From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Thom Brown <thom(at)linux(dot)com> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Differential (transactional) REFRESH |
Date: | 2013-05-14 20:04:53 |
Message-ID: | 1368561893.56557.YahooMailNeo@web162902.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2013-05-14 20:09:47 | Re: Slicing TOAST |
Previous Message | Thom Brown | 2013-05-14 20:01:48 | Re: Differential (transactional) REFRESH |