Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performancepgsql-sqlpgsql-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

pgsql-performance by date

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

pgsql-www by date

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

pgsql-hackers by date

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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group