Re: Differential (transactional) REFRESH

On 14 May 2013 20:55, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 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.

Well that's my point, if 1 row, or 100 million rows were different,
that query would appear to perform the same action, namely to delete
everything from the materialised view, regardless of whether there
were rows that matched. Only if there were no differences would there
be no action. I suspect this isn't the intended query.

--
Thom