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
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 |