Re: REFRESH MATERIALIZED VIEW locklevel

On 2013-03-07 11:50:11 -0800, Kevin Grittner wrote:
> "anarazel(at)anarazel(dot)de" <andres(at)anarazel(dot)de> wrote:
>
> > In the ride home I realized that unless - not that unlikely - you
> > thought about something I didtn't  REFRESH will behave similar to
> > TRUNCATE for repeatable read+ transactions that only access it
> > after REFRESH finished. That is, they will appear empty.
>
> In an early version of the patch someone found that in testing and
> pointed it out.

Cool ;)

> > It would be possible to get different behaviour by immediately
> > freezing all tuples
>
> Which is what I did.

Ok.

> > but that would also result in violations of visibility by showing
> > tuples that are not yet visible.
>
> Which is the case, and should be documented.  (I had not remembered
> to do so yet; I'll tuck away your email as a reminder.)  Since the
> MV is already not guaranteed to be in sync with other data, that
> didn't seem like a fatal flaw.  It is, however, the one case where
> the MV could appear to be *ahead* of the supporting data rather
> than *behind* it.  In a way this is similar to how READ COMMITTED
> transactions can see data from more than one snapshot on write
> conflicts, so I see it as a bigger issue for more strict isolation
> levels -- but those are unlikely to be all that useful with MVs in
> this release anyway.  This is something that I think deserves some
> work in a subsequent release.

I am not that convinced that this is unproblematic. I don't see any
problem with READ COMMITTED but in higher levels Even if you expect the
view to be out-of-date it may very well be surprising to see it
referring to rows in another table that do not yet exists although rows
in that table never get deleted.

Especially in the initial population I don't see any way to get rid of
the problem - I don't think there exists a valid way to compute valid
xmin/xmax values for the resulting tuples of all queries. So unless we
get catalog accesses that completly objeys repeatable read semantics
there's not much we can do about that. And while I think getting rid of
SnapshotNow is realistic, I don't think fully versioned catalog access
is (i.e. it working in a way that you could access a table in the old
and new version after a ALTER TABLE ...).

I wonder if we should add something like indexcheckxmin to matviews
which specifies after which value its valid. Only that it errors out if
you haven't reached it.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services