Re: [HACKERS] [SQL] Materialized View Summary

From: "Jonathan M(dot) Gardner" <jgardner(at)jonathangardner(dot)net>
To: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
Cc: Richard Huxton <dev(at)archonet(dot)com>, pgsql-hackers(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: [HACKERS] [SQL] Materialized View Summary
Date: 2004-02-25 08:19:29
Message-ID: 200402250019.32385.jgardner@jonathangardner.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance pgsql-sql pgsql-www

-----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-----

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message V i s h a l Kashyap @ [Sai Hertz And Control Systems] 2004-02-25 10:05:11 Re: select statement against pg_stats returns inconsistent
Previous Message Neil Conway 2004-02-25 04:47:58 Re: bgwriter never dies

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Gibson 2004-02-25 11:35:47 Re: [HACKERS] Materialized View Summary
Previous Message Steve Atkins 2004-02-25 06:28:37 Re: Slow join using network address function

Browse pgsql-sql by date

  From Date Subject
Next Message Mark Gibson 2004-02-25 11:35:47 Re: [HACKERS] Materialized View Summary
Previous Message Humble Geek 2004-02-25 03:56:22 PLSQL Question regarding multiple inserts

Browse pgsql-www by date

  From Date Subject
Next Message Mark Gibson 2004-02-25 11:35:47 Re: [HACKERS] Materialized View Summary
Previous Message Brett Schwarz 2004-02-25 05:32:10 Re: tcl on gborg, pgtclsh