On 14 May 2013 19:51, Kevin Grittner <kgri...@ymail.com> wrote: > 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?
Wouldn't this either delete everything or nothing, followed by inserting everything or nothing? WHERE NOT EXISTS wouldn't perform any matching, just check to see whether there were matches or no matches. -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
