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
