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
Version: GnuPG v1.2.3 (GNU/Linux)


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to