Differential (transactional) REFRESH

From: Kevin Grittner <kgrittn(at)ymail(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Differential (transactional) REFRESH
Date: 2013-05-14 18:51:53
Message-ID: 1368557513.95389.YahooMailNeo@web162903.mail.bf1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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;

I can see more than one way to code this, but would appreciate
input on the best way sooner rather than later, if anyone is going
to have an opinion.

Thoughts?

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thom Brown 2013-05-14 19:00:26 Re: Slicing TOAST
Previous Message Heikki Linnakangas 2013-05-14 18:47:23 Re: Slicing TOAST