Re: REFRESH MATERIALIZED VIEW locklevel

On 2013-03-07 15:54:32 -0800, Josh Berkus wrote:
> 
> >> Postgres is currently full of fairly innocent-looking commands which
> >> take an unexpected ACCESS EXCLUSIVE lock.  For example, DROP CONSTRAINT
> >> takes an accessexclusive lock, but it hasn't stopped people from using
> >> constraints, and isn't particularly high up on our todo list to fix
> >> it.
> > 
> > Thats a pretty unconvincing comparison. There isn't any expectation that
> > ALTER TABLE works without taking exlusive locks from common
> 
> Not exclusive (which is expected), but AccessExclusive (which catches
> many of our users by surprise).
> 
> How about the fact that dropping an FK constraint takes an
> AccessExclusiveLock on the *referenced* table?

All of that is DDL.

> > implementations and DROP CONSTRAINT only takes a very short time while
> > refreshing a materialized view possibly take rather long.
> 
> Right now there's no expectations at all about our new Matview feature.
>  I think putting the locking information in the docs is the right way to go.

That should definitely be done.

The point is that
a) refreshing is the only way to update materialized views. There's no  incremental support.
b) refreshing will take a long time (otherwise you wouldn't have  create a materialized view) and you can't use the
viewduring that.
 

Which means for anyone wanting to use matviews in a busy environment you
will need to build the new matview separately and then move it into
place via renames. With all the issues that brings like needing to
recreate dependent views and such.

Sorry, but thats not very useful expect (and there very much so) as a
stepping stone for further work.

> > Thats scaring me. Because the current state of the feature isn't
> > something that people expect under the term "materialized views" and I
> > am pretty damn sure people will then remember postgres as trying to
> > provide a tick-box item without it being really usable in the real
> > world.
> > And thats not something I want postgres to be known for.
> 
> We promoted the heck out of binary replication when it was barely
> usable.  We've gotten huge interest in our JSON support, even when it's
> a work-in-progress.  I don't see why I should change an approach to
> advocacy which is clearly working.  What our project considers an
> incomplete feature other OSS DBMSes call a version 2.0.

I heard some people grumble about binary replication in 9.0 but there
were loads of realword scenarios it could be used. I heard quite some
people being annoyed about the level of json support even though it
provided some usefulness with row_to_json (or whatever its called). And
it's a feature that can be extended by extensions. And lots of the
defficiencies of binary replication could be solved by outside tooling.

Thats not possible with matviews as is. Which, again, is *totally fine*
in itself.

Greetings,

Andres Freund

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