Subject: Materialized View Summary

From: "Jonathan M. Gardner" <[email-masked]>
Date: Tue, 24 Feb 2004 08:11:03 -0800

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

Comments and suggestions are definitely welcome.

- -- 
Jonathan Gardner
[email-masked]

---

From: Richard Huxton <[email-masked]>
Date: Tue, 24 Feb 2004 17:11:20 +0000

> http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

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.

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.

-- 
  Richard Huxton
  Archonet Ltd

---

From: Hans-Jürgen Schönig <[email-masked]>
Date: Tue, 24 Feb 2004 18:40:25 +0100

> sure how difficult it is to do in all cases.

Are there any plans to rewrite that in C and add proper support for SQL 
commands? (e.g. "CREATE MATERIALIZED VIEW", "DROP VIEW", ...).

	Best regards,

		Hans

-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

---

From: Chris Gamache <[email-masked]>
Date: Tue, 24 Feb 2004 10:58:06 -0800 (PST)

Tsearch2 comes with its own tsearch2 trigger function. You pass column names to
it, and it puts a vanilla tsvector into the column names in TG_ARGV[0] (zero
based, yes?). Not only can you pass column names to it, but you can pass simple
functions to it as well. This is magical to me. :)

I'm trying to figure out how to do the same thing, except instead of returning
a vanilla tsvector, I want to return a specially weighted tsvector. I've
created a function that can do this:

create or replace function name_vector (text) returns tsvector as '
select setweight(to_tsvector(substr($1,1,strpos($1,'',''))),''C'') ||
to_tsvector(substr($1,strpos($1,'','')+1,length($1)));
' language 'sql';

so... 

Plain:

select to_tsvector('Einstein, Albert');
       to_tsvector
-------------------------
 'albert':2 'einstein':1

Weighted:

select name_vector('Einstein, Albert');
       name_vector
--------------------------
 'albert':2 'einstein':1C

Now, to somehow package that into a magical trigger function... 

All the examples for creating trigger functions that I've found use static
column names, NEW and OLD ... I would like to create a generic trigger
function, as the tsearch2 trigger function does, to return the specially
weighted tsvector.

Its like a lighter to a caveman. Can anyone lend a hand?

CG

__________________________________
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools

---

From: Robert Treat <[email-masked]>
Date: 24 Feb 2004 16:48:49 -0500

> > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

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.

> sure that plenty of people will find this useful. Make sure it gets linked to 

Done. :-)

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

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---

From: Jonathan Gardner <[email-masked]>
Date: Tue, 24 Feb 2004 14:19:39 -0800

On Tuesday 24 February 2004 01:48 pm, Robert Treat wrote:
> > > 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.
> > >

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

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

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. :-)
>

*blush*

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

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

- -- 
Jonathan M. Gardner
Web Developer, Amazon.com
[email-masked] - (206) 266-2906

---

From: "Jonathan M. Gardner" <[email-masked]>
Date: Wed, 25 Feb 2004 00:19:29 -0800

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

---

From: Mark Gibson <[email-masked]>
Date: Wed, 25 Feb 2004 11:35:47 +0000

> Comments and suggestions are definitely welcome.
Fantastic, I was planning on a bit of materialized view investigations 
myself
when time permits, I'm pleased to see you've started the ball rolling.

I was thinking about your problem with mutable functions used in a 
materialized view.

How about eliminating the mutable functions as much as possible from the 
underlying
view definition, and create another view on top of the materialized view 
that has the mutable bits!
Giving you the best of both worlds.

I haven't tried this or thought it through very much - too busy - but 
I'd thought I'd throw
it in for a bit o' head scratching, and chin stroking :)

Cheers
-- 
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

---

From: Robert Treat <[email-masked]>
Date: 25 Feb 2004 10:46:16 -0500

On Wed, 2004-02-25 at 03:19, Jonathan M. Gardner wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 

But I liked the last one :-)

> something like that. I'm interested in how you solved your problem.

Well, I have two different cases actually. In one case I have a master
table with what are essentially 4 or 5 matviews based off of that.  I
don't allow updates to the matviews, only to the master table, and only
via stored procedures. This would work better if locking semantics
inside of pl functions worked properly, but currently we have the
application lock the table in exclusive access mode and then call the
function to make the data changes which then fires off a function to
update the matviews.  Since it's all within a transaction, readers of
the matviews are oblivious to the change.  IMO this whole method is a
wizardry in database hack jobs that I would love to replace.

The second case, and this one being much simpler, started out as a view
that does aggregation across several other views and tables, which is
pretty resource intensive but only returns 4 rows. I refresh the matview
via a cron job which basically does a SELECT * FOR UPDATE on the
matview, deletes the entire contents, then does an INSERT INTO matview
SELECT * FROM view.  Again since it's in a transaction, readers of the
matview are happy (and apps are only granted select on the matview). 
Concurrency is kept because the cron job must wait to get a LOCK on the
table before it can proceed with the delete/update.  I have a feeling
that this method could fall over given a high enough number of
concurrent updaters, but works pretty well for our needs.  

> manually before we touch any of those tables.

Yeah, I ran into similar problems as this, but ISTM you could do a
before update trigger on the matview to do the locking (though I'd guess
this would end in trouble due to plpgsql lock semantics, so maybe i
shouldn't send you down a troubled road...)

> building of the trees will be difficult.

I just noticed that your summary doesn't make use of postgresql RULES in
any way, how much have you traveled down that path? We had cooked up a
scheme for our second case where we would have a table that held an
entry for the matview and then a timestamp of the last update/insert
into any of the base tables the matview depended on.  when then would
create rules on all the base tables to do an update to the refresh table
any time they were updated/inserted/deleted.  We would then put a
corresponding rule on the matview so that on each select from the
matview, it would check to see if any of it's base tables had changed
and if so fire off a refresh of itself.  We ended up abandoning this
idea as the complexity seemed to high when the simple scheme above
worked equally well for our needs. 

Robert Treat

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 2 Mar 2004 11:40:46 -0500 (EST)

Can we get this URL added to the techdocs site please?  Thanks.

---------------------------------------------------------------------------

Jonathan M. Gardner wrote:
[ PGP not available, raw data follows ]
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.3 (GNU/Linux)
> 
> iD8DBQFAO3eZqp6r/MVGlwwRAnpEAKC8+/lFyPBbXetPEfFLwgUvJZLCmgCfYlmR
> 0vZmCcbGSNT/m/W8QOIhufk=
> =snCu
> -----END PGP SIGNATURE-----
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to [email-masked])
> 
[ End of raw data]

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  [email-masked]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---

From: Robert Treat <[email-masked]>
Date: 02 Mar 2004 11:54:12 -0500

Already there, under "Technical Guides and Documents", I gave it a new
section "Materialized Views"

Robert Treat

On Tue, 2004-03-02 at 11:40, Bruce Momjian wrote:
> TIP 7: don't forget to increase your free space map settings