Re: Differential (transactional) REFRESH

On 14 May 2013 19:51, Kevin Grittner <kgrittn@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