From: "Jonathan M. Gardner" <jgardner@jonathangardner.net>
To: Robert Treat <xzilla@users.sourceforge.net>
Subject: Re: [HACKERS] [SQL] Materialized View Summary
Date: Wed, 25 Feb 2004 00:19:29 -0800
User-Agent: KMail/1.6
Cc: Richard Huxton <dev@archonet.com>, pgsql-hackers@postgresql.org,
	pgsql-sql@postgresql.org, pgsql-performance@postgresql.org
References: <200402240811.13013.jgardner@jonathangardner.net>
	<200402241711.20947.dev@archonet.com>
	<1077659329.15366.6061.camel@camel>
In-Reply-To: <1077659329.15366.6061.camel@camel>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: Text/Plain;
  charset="windows-1252"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200402250019.32385.jgardner@jonathangardner.net>

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

I'm not sure if my original reply made it through. Ignore the last one if=
=20
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=20
inserts. It's not a problem unless we do a mass update to the data or=20
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=20
materialized views with an exclusive lock (see Section 12.3 for a=20
reminder on what exactly this means). The locks have to occur in order,=20
so I use a recursive function to traverse a dependency tree to the root=20
and then lock from there. Right now, we only have one materialized view=20
tree, but I can see some schemas having multiple seperate trees with=20
multiple roots. So I put in an ordering to lock the tables in a=20
pre-defined order.

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

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

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

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


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

iD8DBQFAPFqRqp6r/MVGlwwRAnvPAJ90lEEyaBzAfUoLZU93ZDvkojaAwwCdGjaA
YBlO57OiZidZuQ5/S0u6wXM=3D
=3DbMYE
-----END PGP SIGNATURE-----