On Mon, Sep 23, 2013 at 3:45 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Robert, all,
>
> Skipping out on much of the side-discussion to try and drive at the
> heart of this..
>
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> I would suggest that you read the referenced papers for details as to
>> how the algorithm works. To make a long story short, they do need to
>> keep track of what's changed, and how.
>
> I suppose it's good to know that I wasn't completely misunderstanding
> the discussion in Ottawa.
>
>> However, that still seems
>> largely orthogonal to the present discussion.
>
> It *solves* this issue, from where I'm sitting, without any binary
> operators at all.
>
> rows 4, 5, 6 are used to compose matrow 1. When 4, 5, or 6 are updated,
> matrow 1 gets updated.
>
> When the update happens, and it *will* happen (there's no question about
> "oh, should we actually update this record?"), it's a normal PG update
> and all of the values in the row get set to whatever the new values are.
I don't know why there shouldn't be a question about that. Suppose
that the MAX() aggregate is in use. If 4 or 5 or 6 is updated so as
to change the maximum of the three, then matrow 1 needs updating. But
if the maximum remains the same, then it doesn't. The right way to
decide whether it needs updating is to re-aggregate those three rows
and then see whether you get the same (read: binary identical) out of
the aggregate that you got the last time you ran it.
Also, suppose the same statement updates row 4, row 5, and row 6.
Instead of updating the materialized view three times, you do it just
once at end-of-statmement, like an AFTER STATEMENT trigger that
somehow knows which rows were updated. In this case even something
like AVG() could produce the same result as it did before the update.
And you'd surely want to avoid updating the matview if the new value
was the same as what was already stored in the matview (but not if it
was equal but not the same).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company