Re: [HACKERS] [SQL] Materialized View Summary

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not sure if my original reply made it through. Ignore the last one if
it did.

On Tuesday 24 February 2004 1:48 pm, Robert Treat wrote:
> On Tue, 2004-02-24 at 12:11, Richard Huxton wrote:
> > On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote:
> > > I've written a summary of my findings on implementing and using
> > > materialized views in PostgreSQL. I've already deployed eagerly
> > > updating materialized views on several views in a production
> > > environment for a company called RedWeek: http://redweek.com/. As a
> > > result, some queries that were taking longer than 30 seconds to run
> > > now run in a fraction of a millisecond.
> > >
> > > You can view my summary at
> > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.h
> > >tml
>
> have you done much concurrency testing on your snapshot views? I
> implemented a similar scheme in one of my databases but found problems
> when I had concurrent "refresh attempts". I ended up serializing the
> calls view LOCKing, which was ok for my needs, but I thought
> potentially problematic in other cases.
>

We are running into some small problems with deadlocks and multiple
inserts. It's not a problem unless we do a mass update to the data or
something like that. I'm interested in how you solved your problem.

I am playing with an exclusive lock scheme that will lock all the
materialized views with an exclusive lock (see Section 12.3 for a
reminder on what exactly this means). The locks have to occur in order,
so I use a recursive function to traverse a dependency tree to the root
and then lock from there. Right now, we only have one materialized view
tree, but I can see some schemas having multiple seperate trees with
multiple roots. So I put in an ordering to lock the tables in a
pre-defined order.

But if the two dependency trees are totally seperate, it is possible for
one transaction to lock tree A and then tree B, and for another to lock
tree B and then tree A, causing deadlock.

Unfortunately, I can't force any update to the underlying tables to force
this locking function to be called. So we will probably call this
manually before we touch any of those tables.

In the future, it would be nice to have a hook into the locking mechanism
so any kind of lock on the underlying tables can trigger this.

Also, building the dependency trees is completely manual. Until I can get
some functions to actually assemble the triggers and such, automatic
building of the trees will be difficult.

- --
Jonathan Gardner
jgardner(at)jonathangardner(dot)net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAPFqRqp6r/MVGlwwRAnvPAJ90lEEyaBzAfUoLZU93ZDvkojaAwwCdGjaA
YBlO57OiZidZuQ5/S0u6wXM=
=bMYE
-----END PGP SIGNATURE-----