Re: record identical operator

* Kevin Grittner (kgrittn@ymail.com) wrote:
> Stephen Frost <sfrost@snowman.net> wrote:
> > You mean 'at the time of the incremental refresh', right?  Yet that may
> > or may not match running that query directly by an end-user as the plan
> > that a user might get for the entire query could be different than what
> > is run for an incremental update, or due to statistics updates, etc.
>
> I'm confused.  The refresh *does* run the query.  Sure, if the
> query is run again it could return different results.  I'm missing
> the point here.

Perhaps I'm assuming things are farther along than they are..  I was
assumed that 'incremental mat view' updates were actuallly 'partial'-
iow, that it keeps track of the rows in the mat view which are
impacted by the set of rows in the base relations and then runs specific
queries to pull out and operate on the base-rel set-of-rows to update
the specific mat-view-rows.  If it's running the whole query again then
it's certainly more likely to get the same results that the user did,
but it's not a guarantee and that's only a happy coincidence while we're
still doing the whole query approach (which I hope we'd eventually
progress beyond..).

> > I'm trying to figure out why that's a perfectly acceptable solution for
> > users running views with GROUP BYs, but apparently it isn't sufficient
> > for mat views?  In other words, you would suggest telling users "sorry,
> > you can't rely on the value returned by a GROUP BY on that citext column
> > using a normal view, but we're going to try and do better for mat
> > views".
>
> Again, I'm lost. 

Perhaps my reply to Andres will help clear that up?

> If they don't do something to make the result
> deterministic, it could be different on each run of the VIEW, and
> on each REFRESH of the matview.  I don't see why that is an
> argument for trying to suppress the effort needed make the matview
> match the latest run of the query.

Is this really just "run the new query and look if the old and new row
are different" wrt 'incremental' view updates?  If so, I think we're
trying to design something here that we're going to totally break very
shortly when we move beyond that kind of sledgehammer approach to
incremental mat view updates and I'd rather we figure out a solution
which will work beyond that..
> > I don't intend the above to imply that we should never update values in
> > mat views when we can do so in a reliable way to ensure that the value
> > matches what a view would return.  This matches our notion of UPDATE,
> > where we will still UPDATE a value even if the old value and the new
> > value are equal according to the type's equality operator, when the
> > conditional for the UPDATE is using a reliable type (eg: integer).
>
> Well, we provide a trigger function to suppress the UPDATE
> operation if the old and new values are identical -- in terms of
> what is stored.  We do not attempt to use the default btree equals
> operator to suppress updates to different values in some
> circumstances.

This feels like we're trying to figure out how to create a key for a
whole row based on the binary representation of that row and then use
that to check if we should update a given row or not.  This seems a bit
radical, but perhaps that's what we should just do then, rather than
invent binary equivilance operators for what-things-look-like-now for
this- extract the row columns out using their binary _send functions
and then hash the result to build a key that you can use.  At least then
we're using a documented (well, we could probably improve on this, but
still) and stable representation of the data that at least some of our
users already deal with.
Thanks,
    Stephen