Re: Differential (transactional) REFRESH

2013/5/14 Thom Brown <thom(at)linux(dot)com>:
> On 14 May 2013 19:51, Kevin Grittner <kgrittn(at)ymail(dot)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.

depends how much rows is changed. When view is almost stable, then
delete everything can be very slow. But refresh of view can be
implemented with some simplified version of MERGE statement, and it
can be more effective than two independent statements.

>
> --
> Thom
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers