Hash: SHA1

On Tuesday 24 February 2004 01: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.htm
> > >l
> 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.

I don't actually use snapshot views in production. I would imagine that if 
you had two seperate processes trying to update the views simultaneously, 
that would be a problem. All I can say is "don't do that". I think you'd 
want to lock the table before we go and start messing with it on that 

We are running into some deadlock issues and some other problems with eager 
mvs, but they are very rare and hard to reproduce. I think we are going to 
start locking the row before updating it and see if that solves it. We also 
just discovered the "debug_deadlock" feature.

I'll post my findings and summaries of the information I am getting here 

I'm interested in whatever you've been working on WRT materialized views. 
What cases do you think will be problematic? Do you have ideas on how to 
work around them? Are there issues that I'm not addressing but should be?

> > Interesting (and well written) summary. Even if not a "built in"
> > feature, I'm sure that plenty of people will find this useful. Make
> > sure it gets linked to from techdocs.
> Done. :-)


> > If you could identify candidate keys on a view, you could conceivably
> > automate the process even more. That's got to be possible in some
> > cases, but I'm not sure how difficult it is to do in all cases.
> it seems somewhere between Joe Conways work work arrays and polymorphic
> functions in 7.4 this should be feasible.

I'll have to look at what he is doing in more detail.

- -- 
Jonathan M. Gardner
Web Developer, Amazon.com
[EMAIL PROTECTED] - (206) 266-2906
Version: GnuPG v1.2.3 (GNU/Linux)


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

Reply via email to