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
