Re: Differential (transactional) REFRESH - Mailing list pgsql-hackers

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