2 What are Materialized Views? - HSR-Wiki

boreddizzyData Management

Dec 16, 2012 (4 years and 6 months ago)

348 views









Materialized
V
iews in PostgreSQL

Experiments around Jonathan Gardner's Proposal








Seminar Database Systems

Master of Science in Engineering

Major Software and Systems

HSR Hochschule für Technik Rapperswil

ww
w.hsr.ch/mse


Supervisor: Prof. Stefan Keller

Author: Reto Guadagnini


Rapperswil,
December

2011




HSR
HOCHSCHULE FÜR TECHNI K
RAPPERSWI L
FH
O
F
a
c
hhoc
h
s
ch
u
l
e
O
s
t
sc
h
w
e
i z
Materialized Views in PostgreSQL



R. Guadagnini


2

Abstract

Materialized V
iews act like a cache between the client who wants two query a view and the
view. The client
queries the M
aterialized
V
iew instead o
f the ordinary view, which improves
the query performance. Current versions of PostgreSQL do

not have built in support for
Materialized V
iews. Jonathan G
ardner proposed in a paper how Materialized V
iews could be
handcrafted in PostgreSQL. This paper invest
igates his proposal. After a shor
t introduction to
Materialized V
iews it describes two experiments around J. Gardner’s proposal. In the first
experiment the

query performance of an Eager Materialized V
iew was compared with the
query performance of an ordin
ar
y view. We could show that the M
aterialized
V
iew really
improves the query performance. In the second experiment we applied Gardner’s proposal to
a “real” database to show that it is also applicable to real databases.

We were able to apply
Gardner’s prop
osal successfully to a more realistic example database
.


Keywords:

materialized views, views, PostgreSQL, SQL
, relational databases.



Materialized Views in PostgreSQL



R. Guadagnini


3

Table of Contents

1

Introduction

................................
................................
................................
.........................

4

2

What are Materialized Views?

................................
................................
............................

5

2.1

Types of Materia
lized Views

................................
................................
......................

6

2.1.1

Snapshot Materialized Views
................................
................................
.................

6

2.1.2

Eager Materialized Views

................................
................................
......................

7

2.1.3

Very Lazy Materialized Views

................................
................................
................

8

2.1.4

Lazy Materialized Views

................................
................................
........................

8

3

Experiments

................................
................................
................................
........................

9

3.1

Experiment 1: J. Gardner’s Eager Materialized View example

................................
.

9

3.1.1

Measurements

................................
................................
................................
.....

10

3.1.2

Discussion
................................
................................
................................
............

11

3.2

Experiment 2: Eager Materialized View on the “World” database

...........................

12

3.2.1

Import of the “World” database

................................
................................
............

12

3.2.2

Creating basic tables/functions for the Materialized View

................................
...

12

3.2.3

Implement the Materialized View

................................
................................
.........

13

3.2.4

Test the implementation of the Materialized View

................................
...............

15

3.2.5

Discussion
................................
................................
................................
............

15

4

Conclusion

................................
................................
................................
........................

1
6

5

References

................................
................................
................................
.......................

17

6

Appendix

................................
................................
................................
...........................

18

6.1

Figures

................................
................................
................................
.....................

18

6.2

Experiment 1

................................
................................
................................
............

19

6.2.1

SQL script to create the example schema

................................
...........................

19

6.2.2

Random test data generation function

................................
................................
.

23

6.2.3

Benchmark operations

................................
................................
.........................

24

6.2.4

Raw measurement results

................................
................................
...................

25

6.3

Experiment 2

................................
................................
................................
............

26

6.3.1

Basic tables and functions for the Materialized View

................................
..........

26

6.3.2

Materialized V
iew implementation

................................
................................
.......

27

6.3.3

Test of our Eager Materialized View implementation

................................
..........

30




Materialized Views in PostgreSQL



R. Guadagnini


4

1

Introduction

The main purpose of so called “Materialized Views” is to speed up

time consuming

queries

on
views
.

Actual versions of the Oracle Database and the Microsoft SQL Server
have built in
support

for

M
aterialized
V
iews

[4]

[5]
.

Microsoft calls the
M
aterialized
V
i
ews of his SQL Server
“Indexed V
iews”
[5]
. At th
e moment PostgreSQL has no built

in support for
Materialized
V
iews
,

but as shown in a user survey
, thei
r support is

highly requested

by the PostgreSQL
users
[6]
.

Jonathan Gardner suggests in
[1]

how
M
aterialized
V
iews

could be handcrafted

in
PostgreSQL.

The goal of this

work

was

to show that his sugg
estions are applicable to a “real”
example database

and

that

Materialized V
iews really improve the performance

of queries on
views.

In the following we will firs
t give a short introduction to Materialized V
iews in
PostgreSQL according to
[1]
. Then we will describe an experiment, which
we have performed
to show that Materialized V
iews really improve the performance of queries and a second
experiment to show the applicability of Gardner’s suggestions to a “real” example database.





Materialized Views in PostgreSQL



R. Guadagnini


5

2

What are Materialized Views?

A v
iew represents a named query, which can be used in other

queries like a normal table.

Thus a view represents the relation which results, when the underlying query is performed.

In
the case of a normal view this query is
performed again and again each time when the view is
accessed.

Depending on the

underlying

query
,

accessing

the view can be very
expensive and

take a long time.
A “
Materialized View”

instead

is a

real table

which represent
s

a
view.
A
M
aterialized
V
iew cont
ains the content of a

view

and for this reason the underlying query ha
s
not to be performed each time

when

someone accesses the Materialized V
iew.

The M
aterialized
V
iew acts like a cache between the view and its client
s
:



Figur
e
1
: Basic concept of a
“Materialized V
iew


Figur
e
1

shows the basic

concept behind a Materialized V
iew.

We have a set of
t
ables

(table
1 to t
able n)
, called “
Underlying

Tables” and a view which is based o
n these tables. The
M
aterialized
V
iew is based on the view and represents the relation of the view, which is
created by the view when accessing it, in form of a real table.

The Client who wants to query
the view does not run its query on the view.
He runs
his query on the Materialized V
iew
instead.

Thus the query of the client should be performed faster, because it runs

directly on a
real table (the Materialized V
iew) and not on a view, which has to create the relation to query
first
by performing a query o
n the underlying tables.

Materialized Views have also some

drawback
s. One of them

follows directly from their
definition: Materialized Views require memory to store the content of the view
, which they
represent
, thus they need clearly more space

than ordin
ary views.

D
ata
f
low

Table
2

Table n

Table 1



View

Materialized

View

Client

Queries

Query

Materialized Views in PostgreSQL



R. Guadagnini


6

We just introduced th
e basic notion of Materialized V
iews and now we will have a look

at the
d
ifferent types of Materialized V
iews.

2.1

Types

of Materialized V
iews

There are different types of Materialized Vi
ews. They differ in

the way the Materiali
zed V
iew is
created from the view and t
he strategies used to keep the M
aterialized

V
iew in synch with the
underlying tables
. Jonathan Gardner distinguis
hes in his paper four types of Materialized
V
iews

[1]
:



Snapshot



Eager



Lazy



Very Lazy

W
e
will present

these differ
ent types of Materialized V
iews

in more detail

and describe how
they could be implemented in PostgreSQL according to
[1]
.

2.1.1

Snapshot M
ateri
alized V
iews

Snapshot M
aterial
ized V
iews are updated

only on explicit request. This update operation
could be implemented as a

PL/pgSQL

function “refresh_matview” which fi
rst deletes the
content of the Materialized V
iew, queries the view for the
new content and then fills the
M
ateriali
zed V
iew again with th
e new content
[1]
:



Figure
2
: Snapsho
t Materialized V
iew

with function "refresh_matview"

The Snap
s
hot Materialized V
iews are the easiest

ones

to implement
,

because we just

n
e
ed to
create the table for the Materialized V
iew and write the “refresh_matview” function, which
refreshes this table on request
,

to implement one of them.



View

Materialized

View

Function: refresh_matview

Materialized Views in PostgreSQL



R. Guadagnini


7

2.1.2

Eager Materialized V
iews

Eager Materialized V
iews will be updated immediately
,

if the content of
the underlying tables
changes.

J. Gardner suggests in

[1]

the following scheme for their implementation in
PostgreSQL:



Figure
3
:
Implementation s
cheme for

an

Eager Material
ized V
iew

in PostgreSQL

An

E
ager
M
aterialized
V
iew is refreshed by two functions “mv_refresh_row” and “mv_refresh”.
On each of the underlying tables

three

triggers are defined
. There is a trigger for each of the
basic data manipulation operations
:

Insert,
Update and Delete. If necessary, these triggers
call the function “
mv_refresh_row”
in
an appropriate way to keep the Materialized V
iew in
synch with the underlying tables

and thus in synch with the view
.

The “mv_refresh_row”
function expects the Primary Ke
y

(PK)

of the view as parameter, thus we have to identify
the

columns of the view

which

form the Primary Key of the view

to implement the
“mv_refresh_row” function and the triggers.


It is possible, that the vie
w contains data from
some mutable

functions l
ike “now()”.
To

refle
ct changes of this data in the Materialized V
iew
the “mv_refresh” function is used.

The way the functions and triggers have to be implemented depends on the relation
ship
between the tuples in the Materialized V
iew and the tuples in the

underlying tables. There are
five different kinds of relationships
[1]
:



One
-
to
-
one

One tuple in the Materialized V
iew depends on exactly one tuple in
an underlying
table
.

Table 2

Table n

Table 1



View

Materialized

View

mv_refresh_row
(PK
)

mv_refresh
()

Triggers:

Insert

Update

Delete

Triggers:

Insert

U
pdate

Delete

Triggers:

Insert

Update

Delete

Materialized Views in PostgreSQL



R. Guadagnini


8



Many
-
to
-
one

Many tuples in the M
aterialized

V
iew depend

on one tuple in an

underlying table. This
relation typically results
,

if the underlying tables are joined together to create the view.

If we have for example two underlying tables “employee” and “town” which are joined
together to form
an employee

view,
m
any

employee tuples

in the view

have to
change if the postal number of a single town is changed, thus there is
a

many
-
to
-
one
rela
tion between the tuples in the Materialized V
iew and the tuples in an underlying
table.



Many
-
to
-
many

Ma
ny tuples in the M
ateria
lized
V
iew depend on many tuples in an underlying table.
Such relations result if the view is created by a join of the underlying tables and an
aggregation of their tuples.



Other

It is also possible, that we have data in the view which does not come from

a
ny of

the
underlying tables.
The data could come for example from a function like “now()”.

2.1.3

Very Lazy Materialized V
iews

Very Lazy M
ater
ialized Views are similar to Snapshot M
aterialized
V
iews. The
V
ery
Lazy
Materialized V
iew wi
ll be updated only on request

(b
y a call to a function like
“matview_refresh”). To speed up the update process all the changes on the
underlying

tables
will be logged
. To update the
Very L
azy
M
aterialized
V
iew the list of changes since the last
update will be read from the log and the

Materialized V
iew will be updated according to this
list.

2.1.4

Lazy Materialized Views

Lazy Materialized Views are similar to
Very
Lazy Materialized Views

with the difference that
they get updated on commit instead of update on request

like the Very Lazy ones
.

J. Gardner
states in
[1]

that he currently does not know how to implement them in PostgreSQL, so we did
not cover them in our experiments.



Materialized Views in PostgreSQL



R. Guadagnini


9

3

Experiments

In our experiments we concentrated on
ly on one type of Materialized V
iews
, the eager one.

We performed two experiments. The first experiment compares the que
ry performance of
Materialized V
iews with ordinary views and the
second experiment shows how an Eager
Materialized V
iew could be implemented on more realistic example datab
ase.

3.1

Experiment 1:
J.

Gardner
’s
E
ager
Materialized V
iew e
xample

In the first experiment we rebuilt the example
,

which is used in
[1]

to expla
in the
implementation of Eager M
aterial
ized V
iews
,

in PostgreSQL. Then we measur
ed the

performance gain of the Materialized V
iew compared to the

ordinary

view in this example
using some self
-
generated test
tuples
.

The example looks as follows:


The example consists of three
underlying

tables

a

,

b


and

c


and a table

b_mv


which
repr
esents the Materialized V
iew.

Table

a


is in a 1 to n relation to table

b

. Table

b


is in a
1 to n relation to table

c

.

On these three tables the view

b_v


is defined as follows:

CREATE VIEW b_v AS

SELECT b.b_id AS b_id,


a.v AS a_v,


b.v AS b_v,


sum(c.v) AS sum_c_v

FROM a JOIN b USING (a_id) JOIN c USING (b_id)

WHERE (b.expires IS NULL OR b.expires >= now())

GROUP BY b.b_id, a.v, b.v;

Listing
1
: Definition of the view

b_v


[1]

1

n

b

c

a

b_v

b_mv

b_
mv_refresh_row

b_mv_refresh

Trigg
ers:

b_mv_it

b_mv_ut

b_mv_dt

Triggers:

b_mv_it

b_mv_ut

b_mv_dt


Triggers:

b_mv_it

b_mv_ut

b_mv_dt


1

n

Figure
4
: Gardner
’s example for an Eager Materialized V
楥i

Materialized Views in PostgreSQL



R. Guadagnini


10

As we can s
ee from
Listing
1
,

b_v


represents the result of a join of the three tables a, b and
c
. The primary key

b_id


of table

b


identifies each tuple in

the view


b_v


in
a unique
manner, thus
it is used as parameter for the “b_mv_re
fresh_row” function.

Gardner uses in
his examples a special table called
“matview” to keep track of the Materialized V
iews which
have been created
[1]
. Furthermore he defines the functions “create_matview”,
“drop_matview” and “
refresh_matview” which perform operations as expected by their names

and keep track of their operations in the “matview” table
.

3.1.1

Measurements

To compare the performance of

queries on

a
Materialized V
iew and
on
an ordinary view

we
implemented the example on
our

machine. The SQL scripts which we

have

used can be
found in the appendix (under chapter

6.2
).

For our measurements we used a system (laptop) with the following specifications:




CPU
: Intel Core 2 Duo T9550 @ 2.66 GHz



RAM: 4
GB

DDR2 SDRAM @ 1066 MHz



Hard drive: Hitachi HTS 250GB @ 7200 rpm



Operating System: Windows 7 Professional SP 1 (32 bit)



DBMS: PostgreSQL 9.0.5
-
1

with pgAdmin
III

1.12.3


To fill the example database with test
tuples

we wrote a PL/pgSQL function
“generate_t
est_data()” (see chapter
6.2.2
), which generates the

desired number of test tuples

and fills the database with them.

For each number of test
tuples

we measured three times

the
query time

and calculated arithmetic mean

of the me
asurement results

to
compensate
outliers
.

The measurements were performed as follows:


1.

We performed the query “SELECT generate_test_data(n);” to fill the database with n
randomly chosen
tuples
.

2.

We performed three times the query “SELECT * FROM b_v;” on the

ordinary view

b_v


and measured each time the runtime of the query.

3.

We performed three times the query “SELECT * FROM b_
m
v;” on the

M
aterialized
V
iew

b_
m
v


and measured each time the runtime of the query.




Materialized Views in PostgreSQL



R. Guadagnini


11

The following diagram shows the results (arit
hmetic mean) of our measurements for different
numbers of
tuples
:


Figure
5
: Query time
:
Materialized V
iew

versus

ordinary view

(
raw data in

chapter
6.2.4
)

3.1.2

Discussion

Our measurements, depicted in
Figure
5
, give just a trend, because there are many factors
(software version, system workload etc.) which affect them. But the trend show
s that queries
on Materialized V
iews are indeed faster than queries on ordinary views especially

if there are
many tuples

(more than 100’000 in our experiment)

in the view.

For two million tuples the
query time was reduc
ed in our experiment by factor



using the Materialized View
compared to the ordinary view.

While using our “generate_test_data()”

function we noticed

that (as expected)

the
performance of the INSERT operation is diminished
by the Materialized V
iew.
W
e had to add
command
s

to our “gener
ate_test_data()” function which disable th
e triggers of the
Materialized V
iew before the test data insertion and enable them

again

afterwards to get an
acceptable performance.

This shows that the

improved query performance of Materialized
V
iews does not co
me for free. The INSERT, UPDATE and DELETE operations on the
underlying tables bec
ome more expensive if we use a Materialized V
iew.



0

20'000

40'000

60'000

80'000

100'000

120'000

140'000

160'000

180'000

Query time [ms]

Number of tuples

Query on view b_v

Query on materialized view b_mv

Performance

Measurements

Materialized Views in PostgreSQL



R. Guadagnini


12

3.2

Experiment 2:
Eager Materialized V
iew
on the “World” database

For the second experiment we used the “World”
sample
databas
e from P
gFoundry

[8]

to
show how an Eager Materialized V
iew could be implemented on a more realistic database
than the database in experiment 1
.

In the following we
describe how we implemented an
Eager Materialized V
iew on the
“W
orld


database.

3.2.1

Import of the

World


database

First we

cre
ated a new database “World” in P
ostgreSQL on our machine and performed then
the following command to create the

schema of the

“W
orld


database:


psql
-
d world
-
U postgres
-
f
1_
world.sql


T
he

W
orld


database consists of th
ree tables “city”, “country” and “countrylanguage”:

Figure
6
:
Tables of the

World


database

The Primary Keys (PK) of
the
tables are:



“city.id” for city



“country.code” for country



countrylanguage has a
primary key

composed of “countrylanguage.countrycode” and
“countrylanguage.language”

3.2.2

Creating
basic tables/functions for the Materialized V
iew

Second

we created the “matviews” table and the associated functions “create_matview”,
“drop_matview” and “refresh
_matview”

wh
ich

were also used in the example from experiment
1 and

were published in
[1]
. The “matviews”

table
keeps track of the Materialized V
ie
ws which
have been created. T
he function

“create_matview(v)” creates a Materiali
zed V
iew for the view
“v”
,
“drop_matview(mv)” deletes the Materialized V
iew “mv” and “
refresh_matview(mv)” fills
the Materialized V
iew “mv”

with the actual content of its associated view.



country

countrylanguage

city

1

1

1

n

Materialized Views in PostgreSQL



R. Guadagnini


13

3.2.3

Implement the Materialized V
iew

Now our database is ready for the
creation of

a view and a corresponding Materialized V
iew.
We defined a view “country_v” on the tables “city”, “country” and “countrylanguage” as
follows:

CREATE VIEW country_v AS

SELECT city.id AS city_id, city.name AS city_capital, country.code
AS c_code
,


country.capital AS c_capital_id, country.name AS c_name,


countrylanguage.countrycode AS cl_code,


countrylanguage.language AS cl_language,
countrylanguage.percentage


FROM



country, city, countrylanguage


WHERE



city.id = country.capital



AND



country.code = countrylanguage.countrycode;

Listing
2
:
The

view

“country_v” defined on the

World


database

Now we can create

the corresponding Materialized V
iew “country_mv” using the
“create_matview” function:


SELECT create_ma
tview('country_mv', 'country_v');


To
implement the triggers for the Materialized V
iew

“country_mv”

we must identify the
primary
k
ey of the associated view “country_v”. The primary key of

country_v


is composed of the
columns “cl_code” and “cl_language”.

We define the “country_mv_refresh_row” function as
follows:


CREATE FUNCTION


country_mv_refresh_row(country_mv.cl_code%TYPE,
country_mv.cl_language%TYPE)


RETURNS VOID

SECURITY DEFINER

LANGUAGE 'plpgsql' AS '

BEGIN


DELETE FROM country_mv WHERE cl_code
= $1 AND cl_language = $2;


INSERT INTO country_mv SELECT * FROM country_v WHERE cl_code = $1
AND cl_language = $2;


RETURN;

END

';

Listing
3
:
The “mv
_refresh_row” function for our Materialized V
iew

The “mv_refresh_row” functio
n expects the primary key of the view as paramete
r and updates
the tuple in the M
aterialized

View

which is identified by this key.

We do not need to define the function “mv_refresh”, which was used in the example of
experiment 1, because our view “country_
v” does not depend on mutable functions.



Materialized Views in PostgreSQL



R. Guadagnini


14

Finally we implement the triggers for our
Materialized V
iew.

We create an INSERT, UPDATE
and DELETE trigger for each of the underlying tables. To show how such a trigger is
implemented we look at the INSERT trigge
r of the city table:


CREATE FUNCTION country_mv_city_it() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM country_mv_refresh_row(code, language)


FROM



(SELECT * FROM country, countrylanguage WHERE code =
countrycode) AS x


WHERE



capital = NEW.id;


RETURN NULL;

END

';

CREATE TRIGGER country_mv_it AFTER INSERT ON city


FOR EACH ROW EXECUTE PROCEDURE country_mv_city_it();

Listing
4
:
The INSERT trigger for the “city” table

The INSERT trigger is called
each time when a
tuple

is inserted in the “city” table. He calls the
function “count
ry_mv_city_it()” to update the Materialized V
iew

according to the inserted
tuple. The function “country_mv_city_it()” determines the primary key of all rows of the
material
ized view which are affected by the insertion of the new tuple and calls the function
“country_mv_refresh_row(PK)” with the primary key of these
rows

as p
arameter to update
them in the Materialized V
iew.

T
he whole implementation of our Materialized V
iew ca
n be found in the appendix (chapter
6.3.2
). The Materialized V
iew we have created in our second experiment could be depicted as
foll
ows:

Figure
7
:
Eager Materialized V
iew on the

World


database

country

countrylanguage

city

country_v

country_mv

country_mv_refresh_row

Trigg
ers:

country_mv_it

country_mv_ut

country_mv_dt

Triggers:

country_mv_it

coun
try_mv_ut

country_mv_dt


Triggers:

country_mv_it

country_mv_ut

country_mv_dt


1

1

1

n

Materialized Views in PostgreSQL



R. Guadagnini


15

3.2.4

Test t
he implementation of the
Materialized V
iew

Even if we the

World


database is
quite
simple the implementation of a Materialized V
iew on
it is error prone. To test our implementation we used the commands described in chapter
6.3.3
. We have test
ed

if our implementation behaves as expected when we p
erform data
manipulation operations

(INSERT, UPDATE and DELETE) on the underlying tables of
our
Materialized View
.


3.2.5

Discussion

As showed w
e could realize a Materialized V
iew as proposed
by J. Gardner in
[1]

on a more
realistic database. The

World


database we used is quite simple but even with such a simple
da
tabase the implementation of a Materialized V
iew is not really easy.

For more complex
dat
abases the i
mplementation of a Materialized V
iew could be really difficult.

The steps to
create an Eager Materialized V
iew could be summarized as follows:

1.

Create the view if it is not already created
.

2.

Identify the primary key of the view.

3.

Create the basic tables/funct
ions “matviews”, “create_matview()”, “drop_matview()”
and “refresh_matview()” published in Gardner’s

paper
[1]
.

4.

Create the table for the Materialized V
iew using the “create_matview()” function.

5.

Implement the “mv_refresh_row
()

function with the primary key of the view as
argument.

6.

I
f the view bases on some mut
able

functions

like “now()”

implement the function
“mv_refres
h
()”.

7.

Implement all INSERT, UPDATE and DELETE triggers on the underlying tables.

8.

Finally t
est if the Materializ
ed View behaves as expected when INSERT, UPDATE
and DELETE operations are performed on the underlying tables.




Materialized Views in PostgreSQL



R. Guadagnini


16

4

Conclusion

We showed
that J. Gardner
’s approach to realize Eager Materialized V
iews in PostgreSQL is
applicable to “real” databases

and that th
e query performance could be

im
proved by using a
Materialized V
iew

(up to factor



in our experiment)
. The
main
drawback of this kind of
Materialized V
iews is
,

that they are not easy to implement.

Materialized V
iews improve on
one side the query performan
ce but on the other side they decrease the performance of data
manipulation operations on the underlying tables.
Another drawback of the Materialized Views
is that they consume more memory than ordinary views. These drawbacks should be kept in
mind when ap
plying Materialized Views.

Other database management system
s

like the Oracle Database or the Microsoft SQL Server
have built

in support for Materialized V
iew
s

[4]
[5]
, which simplifies the creation

o
f Materialized
V
iews.

The
y also offer even more sophisticated features. For example the Microsoft SQL
Server supports d
ata manipulation operations on Materialized V
iews

[5]

while the Materialized
V
ie
ws described in this paper c
an

only be used in a read only
fashion
.

We
th
ink that built in support for Materialized V
iews would be a good feature for PostgreSQL

as it could make things a lot easier
.

[3]

gives
some ideas how the support for Materialized
V
i
ews could be built into a database management system.




Materialized Views in PostgreSQL



R. Guadagnini


17

5

References

[1]

Jonathan Gardner: PostgreSQL/Materialized Views. Retrieved September
20
,

2011, from

http://tech.jonathangard
ner.net/wiki/PostgreSQL/Materialized_Views

[2]

Dan Chak: Materialized Views that

Really

work. PGCon 2008. Retrieved November 10,
2011, from
http://www.pgcon.org/2008/schedule/events/69.en.html

[3]

Ashish Gupta,
Inderpal Singh Mumick
:
Materialized Views: Techniques, Implementations,
and Applications
. The MIT Press
. First Edition,

1999.

[4]

Oracle Database Data Warehousing Guide
, 10g Release 2.

Retrieved November 28, 2011, from
http://docs.oracle.com/cd/B19306_01/server.102/b14223/basicmv.htm#g1028195

[5]

Eric Hanson, Susan Price, et. al.: Improving Performance with SQL Server 2008 Indexed
Views.
Microsoft Developer
Network.

Retrieved November 28, 2011, from

http://msdn.microsoft.com/en
-
us/library/dd171921%28v=sql.100%29.aspx

[6]

http://wiki.postgresql.org/wiki/Materialized_Views
. Retrieved November 10, 2011.

[7]

http://www.if
-
not
-
true
-
then
-
false.com/2010/po
stgresql
-
select
-
a
-
random
-
number
-
in
-
a
-
range
-
between
-
two
-
numbers/
. Retrieved November 25, 2011.

[8]

http://pgfoundry.org/projects/dbsamples/
. Retrieved November 2
8
, 2011.



Materialized Views in PostgreSQL



R. Guadagnini


18

6

Appendix

6.1

Figures

Figure 1: Basic concept of a “Materialized View”

................................
................................
......

5

Figure 2: Snapshot Materialized View with function "refresh_matview"

................................
....

6

Figure 3: Implementation scheme for an Eager Materialized View in PostgreSQL

..................

7

Figure 4: Gardner’s example for an Eager Materialized View

................................
...................

9

Figure 5: Query time: Materialized View versus ordinary view (raw data in chapter 6.2.4)

....

11

Figure 6: T
ables of the “World” database

................................
................................
................

12

Figure 7: Eager Materialized View on the “World” database

................................
...................

14




Materialized Views in PostgreSQL



R. Guadagnini


19

6.2

Experiment 1

6.2.1

SQL script to create the examp
le schema

The following script creates the
Eager Materialized V
iew example from Gardner’s paper. It is
copied

together from various listings published in Gardner’s paper
[1]
:


CREATE TABLE matviews (


mv_name NAME NOT NULL PRI
MARY KEY


, v_name NAME NOT NULL


, last_refresh TIMESTAMP WITH TIME ZONE

);



CREATE OR REPLACE FUNCTION create_matview(NAME, NAME)


RETURNS VOID


SECURITY DEFINER


LANGUAGE plpgsql AS '


DECLARE


matview ALIAS FOR $1;


view_name ALIAS FOR $2;



entry matviews%ROWTYPE;


BEGIN


SELECT * INTO entry FROM matviews WHERE mv_name = matview;




IF FOUND THEN


RAISE EXCEPTION ''Materialized view ''''%'''' already
exists.'',


matview;


END IF;




EXECUTE ''REVOKE ALL O
N '' || view_name || '' FROM PUBLIC'';




EXECUTE ''GRANT SELECT ON '' || view_name || '' TO PUBLIC'';




EXECUTE ''CREATE TABLE '' || matview || '' AS SELECT * FROM ''
|| view_name;




EXECUTE ''REVOKE ALL ON '' || matview || '' FROM PUBLIC''
;




EXECUTE ''GRANT SELECT ON '' || matview || '' TO PUBLIC'';




INSERT INTO matviews (mv_name, v_name, last_refresh)


VALUES (matview, view_name, CURRENT_TIMESTAMP);




RETURN;


END


';



CREATE OR REPLACE FUNCTION drop_matview(NA
ME) RETURNS VOID


SECURITY DEFINER


LANGUAGE plpgsql AS '


DECLARE


matview ALIAS FOR $1;


entry matviews%ROWTYPE;


BEGIN




SELECT * INTO entry FROM matviews WHERE mv_name = matview;




IF NOT FOUND THEN


RAISE EXCEPTION ''Material
ized view % does not exist.'',
matview;


END IF;



Materialized Views in PostgreSQL



R. Guadagnini


20


EXECUTE ''DROP TABLE '' || matview;


DELETE FROM matviews WHERE mv_name=matview;




RETURN;


END


';



CREATE OR REPLACE FUNCTION refresh_matview(name) RETURNS VOID


SECURITY DEFINER


LANG
UAGE plpgsql AS '


DECLARE


matview ALIAS FOR $1;


entry matviews%ROWTYPE;


BEGIN




SELECT * INTO entry FROM matviews WHERE mv_name = matview;




IF NOT FOUND THEN


RAISE EXCEPTION ''Materialized view % does not exist.'',
matview;


END IF;



EXECUTE ''DELETE FROM '' || matview;


EXECUTE ''INSERT INTO '' || matview


|| '' SELECT * FROM '' || entry.v_name;



UPDATE matviews


SET last_refresh=CURRENT_TIMESTAMP


WHERE mv_name=matview;



RETURN;

END

'
;



CREATE TABLE a (


a_id INT PRIMARY KEY,


v INT

);

CREATE TABLE b (


b_id INT PRIMARY KEY,


a_id INT REFERENCES a,


v INT,


expires TIMESTAMP

);

CREATE TABLE c (


c_id INT PRIMARY KEY,


b_id INT REFERENCES b,


v INT

);



CREATE VIEW b_v AS

SEL
ECT b.b_id AS b_id,


a.v AS a_v,


b.v AS b_v,


sum(c.v) AS sum_c_v

FROM a JOIN b USING (a_id) JOIN c USING (b_id)

WHERE (b.expires IS NULL OR b.expires >= now())

GROUP BY b.b_id, a.v, b.v;


SELECT create_matview('b_mv', 'b_v');



CREATE FUNCTION b_mv_re
fresh_row(b_mv.b_id%TYPE) RETURNS VOID

SECURITY DEFINER

LANGUAGE 'plpgsql' AS '

BEGIN

Materialized Views in PostgreSQL



R. Guadagnini


21


DELETE FROM b_mv WHERE b_id = $1;


INSERT INTO b_mv SELECT * FROM b_v WHERE b_id = $1;


RETURN;

END

';



CREATE FUNCTION b_mv_refresh() RETURNS VOID


SECURITY DEFINER


LANGUAGE 'plpgsql' AS '


BEGIN


PERFORM b_mv_refresh_row(b_id)


FROM b, matviews


WHERE matviews.mv_name = ''b_mv''


AND b.expires >= matviews.last_refresh


AND b.expires < now();




UPDATE matviews



SET last_refresh = now()


WHERE mv_name = ''b_mv'';




RETURN;


END


';



--

a triggers

CREATE FUNCTION b_mv_a_ut() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


IF OLD.a_id = NEW.a_id THEN


PERFORM b_mv_refresh_row(b.b_
id) FROM b WHERE b.a_id = NEW.a_id;


ELSE


PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = OLD.a_id;


PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = NEW.a_id;


END IF;


RETURN NULL;

END

';

CREATE TRIGGER b_mv_ut AFTER UPDATE ON a


FOR EACH ROW EXECUTE PROCEDURE b_mv_a_ut();


CREATE FUNCTION b_mv_a_dt() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = OLD.a_id;


RETURN NULL;

END

';

CREATE TRIGGER b_mv_dt AFTER D
ELETE ON a


FOR EACH ROW EXECUTE PROCEDURE b_mv_a_dt();


CREATE FUNCTION b_mv_a_it() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = NEW.a_id;


RETURN NULL;

END

';

CREATE TRIGGER b_
mv_it AFTER INSERT ON a


FOR EACH ROW EXECUTE PROCEDURE b_mv_a_it();


--

b triggers

CREATE FUNCTION b_mv_b_ut() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


IF OLD.b_id = NEW.b_id THEN


PERFORM b_mv_refresh_row(NEW.b_id);


ELSE

Materialized Views in PostgreSQL



R. Guadagnini


22



PERFORM b_mv_refresh_row(OLD.b_id);


PERFORM b_mv_refresh_row(NEW.b_id);


END IF;


RETURN NULL;

END

';

CREATE TRIGGER b_mv_ut AFTER UPDATE ON b


FOR EACH ROW EXECUTE PROCEDURE b_mv_b_ut();


CREATE FUNCTION b_mv_b_dt() RETURNS TRIGGER

SECURITY DEFI
NER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM b_mv_refresh_row(OLD.b_id);


RETURN NULL;

END

';

CREATE TRIGGER b_mv_dt AFTER DELETE ON b


FOR EACH ROW EXECUTE PROCEDURE b_mv_b_dt();


CREATE FUNCTION b_mv_b_it() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'pl
pgsql' AS '

BEGIN


PERFORM b_mv_refresh_row(NEW.b_id);


RETURN NULL;

END

';

CREATE TRIGGER b_mv_it AFTER INSERT ON b


FOR EACH ROW EXECUTE PROCEDURE b_mv_b_it();


--

c triggers

CREATE FUNCTION b_mv_c_ut() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpg
sql' AS '

BEGIN


IF OLD.b_id = NEW.b_id THEN


PERFORM b_mv_refresh_row(NEW.b_id);


ELSE


PERFORM b_mv_refresh_row(OLD.b_id);


PERFORM b_mv_refresh_row(NEW.b_id);


END IF;


RETURN NULL;

END

';

CREATE TRIGGER b_mv_ut AFTER UPDATE ON c


FOR EACH

ROW EXECUTE PROCEDURE b_mv_c_ut();


CREATE FUNCTION b_mv_c_dt() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM b_mv_refresh_row(OLD.b_id);


RETURN NULL;

END

';

CREATE TRIGGER b_mv_dt AFTER DELETE ON c


FOR EACH ROW EXECUTE PRO
CEDURE b_mv_c_dt();


CREATE FUNCTION b_mv_c_it() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM b_mv_refresh_row(NEW.b_id);


RETURN NULL;

END

';

CREATE TRIGGER b_mv_it AFTER INSERT ON c


FOR EACH ROW EXECUTE PROCEDURE b_mv_c_it
();


Materialized Views in PostgreSQL



R. Guadagnini


23

6.2.2

Random test data generation function

We wrote for our little benchmark a
PL/pgSQL function

which fills

the

example
database with
random datasets, so that there are n tuples in the view


b_v


and thus also n tuples in the
corresponding materialized vi
ew

b_mv

:


--

Function: generate_test_data(integer)


--

DROP FUNCTION generate_test_data(integer);


CREATE OR REPLACE FUNCTION generate_test_data(n integer)


RETURNS void AS

$BODY$

DECLARE


rnd_nr integer;


rnd_a_for_b integer;


rnd_c_for_b integer;



index_of_curr_tpl_in_c integer := 1;

BEGIN



--

Disable triggers
-------------------------
---------------------


RAISE NOTICE 'Disabling all triggers which update the matview...';


ALTER TABLE a DISABLE TRIGGER b_mv_dt;


ALTER TABLE a DISABLE TRIGGER
b_mv_it;


ALTER TABLE a DISABLE TRIGGER b_mv_ut;



ALTER TABLE b DISABLE TRIGGER b_mv_dt;


ALTER TABLE b DISABLE TRIGGER b_mv_it;


ALTER TABLE b DISABLE TRIGGER b_mv_ut;



ALTER TABLE c DISABLE TRIGGER b_mv_dt;


ALTER TABLE c DISABLE TRIGGER b_mv_it;


ALTER TABLE c DISABLE TRIGGER b_mv_ut;



--

Delete the old test data contained in the tables a, b and c.
--


RAISE NOTICE 'Cleaning tables...';


--

We use truncate because it is much faster than DELETE FROM
TABLE.


TRUNCATE a, b, c;


RAISE NOTICE '
Tables cleaned.';



--

Fill the tables a,b and c with new random
data.
---------------


--

Fill table a with n datasets:


RAISE NOTICE 'Generate % new test datasets...', n;


RAISE NOTICE 'Filling table a...';


FOR i in 1 .. n LOOP


SELECT INTO rnd_
nr get_random_number(0, 99);


INSERT INTO a (a_id, v) VALUES (i, rnd_nr);


END LOOP;



--

Fill table b with n datasets:


RAISE NOTICE 'Filling table b...';


FOR i in 1 .. n LOOP


SELECT INTO rnd_nr get_random_number(0, 99);


--

Give the curren
t tuple of b a random tuple of a


--

(Some tuples of a will not get a tuple of b, but this isn't a


--

problem for the benchmark)


SELECT INTO rnd_a_for_b get_random_number(1,n);


INSERT INTO b (b_id, a_id, v) VALUES (i, rnd_a_for_b, rnd_nr)
;


END LOOP;



--

Fill table c with n datasets:


RAISE NOTICE 'Filling table c...';


FOR i in 1 .. n LOOP



--

Give eache tuple of b between 1 and 5 tuples of c


SELECT INTO rnd_c_for_b get_random_number(1,5);


FOR j in 1 .. rnd_c_for_b LOOP

Materialized Views in PostgreSQL



R. Guadagnini


24



--

RAISE NOTICE 'i: %., j: %.', i, j;


SELECT INTO rnd_nr get_random_number(0, 99);


INSERT INTO c (c_id, b_id, v) VALUES (index_of_curr_tpl_in_c,
i, rnd_nr);


index_of_curr_tpl_in_c := index_of_curr_tpl_in_c + 1;


j := j+1;


EN
D LOOP;




END LOOP;



--

Refresh the matview after testdata generation and reenable all
triggers
--


RAISE NOTICE 'Refreshing matview...';


PERFORM refresh_matview('b_mv');



RAISE NOTICE 'Reenabling all triggers which update the
matview...';


AL
TER TABLE a ENABLE TRIGGER b_mv_dt;


ALTER TABLE a ENABLE TRIGGER b_mv_it;


ALTER TABLE a ENABLE TRIGGER b_mv_ut;



ALTER TABLE b ENABLE TRIGGER b_mv_dt;


ALTER TABLE b ENABLE TRIGGER b_mv_it;


ALTER TABLE b ENABLE TRIGGER b_mv_ut;



ALTER TABLE c EN
ABLE TRIGGER b_mv_dt;


ALTER TABLE c ENABLE TRIGGER b_mv_it;


ALTER TABLE c ENABLE TRIGGER b_mv_ut;




RAISE NOTICE 'Testdata generation finished.';



RETURN;

END

$BODY$


LANGUAGE plpgsql VOLATILE SECURITY DEFINER


COST 100;

ALTER FUNCTION generate_t
est_d
ata(integer) OWNER TO postgres;



Our function
calls

“get_random_number” which we have taken from
[7]
:


CREATE OR REPLACE FUNCTION get_random_number(INTEGER, INTEGER)
RETURNS INTEGER AS $$

DECLARE


start_int ALIAS FOR
$1;


end_int ALIAS FOR $2;

BEGIN


RETURN trunc(random() * (end_int
-
start_int) + start_int);

END;

$$ LANGUAGE 'plpgsql' STRICT;


6.2.3

Benchmark operations

To get our

performance

measurements we executed the following three SQL statements for
various numbe
rs n and noticed their runtime:

--

Fill the database with test datasets

SELECT generate_test_data(n
);


--

Query the view b_v

SELECT * FROM b_v;


--

Query the materialized view b_mv

SELECT * FROM b_mv;



Materialized Views in PostgreSQL



R. Guadagnini


25

6.2.4

Raw measurement results




Materialized Views in PostgreSQL



R. Guadagnini


26

6.3

Experiment 2

6.3.1

Basi
c table
s and functions for the Materialized V
iew

For our own example we reused the

matviews


table and the functions “create_matview”,
“drop_matview” and “refresh_matview” from
[1]
:

CREATE TABLE matviews (


mv_name NAME NOT NULL PRI
MARY KEY


, v_name NAME NOT NULL


, last_refresh TIMESTAMP WITH TIME ZONE

);



CREATE OR REPLACE FUNCTION create_matview(NAME, NAME)


RETURNS VOID


SECURITY DEFINER


LANGUAGE plpgsql AS '


DECLARE


matview ALIAS FOR $1;


view_name ALIAS FOR $2;



entry matviews%ROWTYPE;


BEGIN


SELECT * INTO entry FROM matviews WHERE mv_name = matview;




IF FOUND THEN


RAISE EXCEPTION ''Materialized view ''''%'''' already
exists.'',


matview;


END IF;




EXECUTE ''REVOKE ALL O
N '' || view_name || '' FROM PUBLIC'';




EXECUTE ''GRANT SELECT ON '' || view_name || '' TO PUBLIC'';




EXECUTE ''CREATE TABLE '' || matview || '' AS SELECT * FROM ''
|| view_name;




EXECUTE ''REVOKE ALL ON '' || matview || '' FROM PUBLIC''
;




EXECUTE ''GRANT SELECT ON '' || matview || '' TO PUBLIC'';




INSERT INTO matviews (mv_name, v_name, last_refresh)


VALUES (matview, view_name, CURRENT_TIMESTAMP);




RETURN;


END


';




CREATE OR REPLACE FUNCTION drop_matview(N
AME) RETURNS VOID


SECURITY DEFINER


LANGUAGE plpgsql AS '


DECLARE


matview ALIAS FOR $1;


entry matviews%ROWTYPE;


BEGIN




SELECT * INTO entry FROM matviews WHERE mv_name = matview;




IF NOT FOUND THEN


RAISE EXCEPTION ''Materia
lized view % does not exist.'',
matview;


END IF;




EXECUTE ''DROP TABLE '' || matview;


DELETE FROM matviews WHERE mv_name=matview;

Materialized Views in PostgreSQL



R. Guadagnini


27




RETURN;


END


';



CREATE OR REPLACE FUNCTION refresh_matview(name) RETURNS VOID


SECURITY DEFINER


LAN
GUAGE plpgsql AS '


DECLARE


matview ALIAS FOR $1;


entry matviews%ROWTYPE;


BEGIN




SELECT * INTO entry FROM matviews WHERE mv_name = matview;




IF NOT FOUND THEN


RAISE EXCEPTION ''Materialized view % does not exist.'',
matview
;


END IF;



EXECUTE ''DELETE FROM '' || matview;


EXECUTE ''INSERT INTO '' || matview


|| '' SELECT * FROM '' || entry.v_name;



UPDATE matviews


SET last_refresh=CURRENT_TIMESTAMP


WHERE mv_name=matview;



RETURN;

END

'

6.3.2

Materialized V
iew implementation

The following script conta
ins the definition of our own Eager Materialized V
iew example
based on the

World


database:

--

View country_v
--------------------------------------------------

CREATE VIEW country_v AS

SELECT

city.id AS city_id, city.name AS city_capital, country.code
AS c_code,


country.capital AS c_capital_id, country.name AS c_name,


countrylanguage.countrycode AS cl_code,


countrylanguage.language AS cl_language,
countrylanguage.percentage


FROM



country
, city, countrylanguage


WHERE



city.id = country.capital



AND



country.code = countrylanguage.countrycode;



--

Materialized view country_mv
------------------------------------

SELECT create_matview('country_mv', 'country_v');



--

country_mv_refre
sh_row function
---------------------------------

CREATE FUNCTION


country_mv_refresh_row(country_mv.cl_code%TYPE,
country_mv.cl_language%TYPE)


RETURNS VOID

SECURITY DEFINER

LANGUAGE 'plpgsql' AS '

BEGIN


DELETE FROM country_mv WHERE cl_code = $1 AND cl_
language = $2;

Materialized Views in PostgreSQL



R. Guadagnini


28


INSERT INTO country_mv SELECT * FROM country_v WHERE cl_code = $1
AND cl_language = $2;


RETURN;

END

';


--

triggers
--------------------------------------------------------

--

city triggers

CREATE FUNCTION country_mv_city_ut() RETURNS TR
IGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


IF OLD.id = NEW.id THEN


PERFORM country_mv_refresh_row(code, language)


FROM



(SELECT * FROM country, countrylanguage WHERE code =
countrycode) AS x


WHERE



capital = NEW.id;



ELSE


PERFORM
b_mv_refresh_row(code, language)


FROM



(SELECT * FROM country, countrylanguage WHERE code =
countrycode) AS x


WHERE



capital = OLD.id;


PERFORM country_mv_refresh_row(code, language)


FROM



(SELECT * FROM country, countrylanguage WHERE code =
count
rycode) AS x


WHERE



capital = NEW.id;


END IF;


RETURN NULL;

END

';

CREATE TRIGGER country_mv_ut AFTER UPDATE ON city


FOR EACH ROW EXECUTE PROCEDURE country_mv_city_ut();


CREATE FUNCTION country_mv_city_dt() RETURNS TRIGGER

SECURITY DEFINER LANGUAG
E 'plpgsql' AS '

BEGIN


PERFORM country_mv_refresh_row(code, language)


FROM



(SELECT * FROM country, countrylanguage WHERE code =
countrycode) AS x


WHERE capital = OLD.id;


RETURN NULL;

END

';

CREATE TRIGGER country_mv_dt AFTER DELETE ON city


FOR EA
CH ROW EXECUTE PROCEDURE country_mv_city_dt();


CREATE FUNCTION country_mv_city_it() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM country_mv_refresh_row(code, language)


FROM



(SELECT * FROM country, countrylanguage WHERE code

=
countrycode) AS x


WHERE



capital = NEW.id;


RETURN NULL;

END

';

CREATE TRIGGER country_mv_it AFTER INSERT ON city


FOR EACH ROW EXECUTE PROCEDURE country_mv_city_it();


--

country triggers

CREATE FUNCTION country_mv_country_ut() RETURNS TRIGGER

Materialized Views in PostgreSQL



R. Guadagnini


29

SEC
URITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


IF OLD.code = NEW.code THEN


PERFORM country_mv_refresh_row(countrycode, language)


FROM



countrylanguage


WHERE



countrycode = NEW.code;


ELSE


PERFORM country_mv_refresh_row(countrycode, language)


F
ROM



countrylanguage


WHERE



countrycode = OLD.code;


PERFORM b_mv_refresh_row(countrycode, language)


FROM



countrylanguage


WHERE



countrycode = NEW.code;


END IF;


RETURN NULL;

END

';

CREATE TRIGGER country_mv_ut AFTER UPDATE ON country


FOR E
ACH ROW EXECUTE PROCEDURE country_mv_country_ut();


CREATE FUNCTION country_mv_country_dt() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM country_mv_refresh_row(countrycode, language)


FROM



countrylanguage


WHERE



countrycode

= OLD.code;


RETURN NULL;

END

';

CREATE TRIGGER country_mv_dt AFTER DELETE ON country


FOR EACH ROW EXECUTE PROCEDURE country_mv_country_dt();


CREATE FUNCTION country_mv_country_it() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PER
FORM country_mv_refresh_row(countrycode, language)


FROM



countrylanguage


WHERE



countrycode = NEW.code;


RETURN NULL;

END

';

CREATE TRIGGER country_mv_it AFTER INSERT ON country


FOR EACH ROW EXECUTE PROCEDURE country_mv_country_it();


--

countrylan
guage triggers

CREATE FUNCTION country_mv_countrylanguage_ut() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


IF OLD.countrycode = NEW.countrycode AND OLD.language =
NEW.language THEN


PERFORM country_mv_refresh_row(NEW.countrycode, NE
W.language);


ELSE


PERFORM country_mv_refresh_row(OLD.countrycode, OLD.language);


PERFORM country_mv_refresh_row(NEW.countrycode, NEW.language);


END IF;


RETURN NULL;

END

';

CREATE TRIGGER country_mv_ut AFTER UPDATE ON countrylanguage

Materialized Views in PostgreSQL



R. Guadagnini


30


FOR EAC
H ROW EXECUTE PROCEDURE country_mv_countrylanguage_ut();


CREATE FUNCTION country_mv_countrylanguage_dt() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM country_mv_refresh_row(OLD.countrycode, OLD.language);


RETURN NULL;

END

';

CREATE TRIGGER country_mv_dt AFTER DELETE ON countrylanguage


FOR EACH ROW EXECUTE PROCEDURE country_mv_countrylanguage_dt();


CREATE FUNCTION country_mv_countrylanguage_it() RETURNS TRIGGER

SECURITY DEFINER LANGUAGE 'plpgsql' AS '

BEGIN


PERFORM count
ry_mv_refresh_row(NEW.countrycode, NEW.language);


RETURN NULL;

END

';

CREATE TRIGGER country_mv_it AFTER INSERT ON countrylanguage


FOR EACH ROW EXECUTE PROCEDURE country_mv_countrylanguage_it();

6.3.3

Test of our

Eager Materialized V
iew implementation

To te
st our own
Eager Materialized V
iew implementation for the

World


database we used
the following SQL statements:

--

Both queries should return the empty relation

SELECT * FROM country_v WHERE c_code = 'WON';

SELECT * FROM country_mv WHERE c_code = 'WON';


--

Test INSERT triggers
--------------------------------------------

INSERT INTO city (id, name, countrycode, district, population)


VALUES
(5000, 'Caprica', 'WON', 'qwertz
', 100);

INSERT INTO country (code, continent, region, name, capital,
surfacearea, p
opulation, localname, governmentform, code2)


VALUES ('WON',
'Europe', 'Western Europe', 'Won
derland', 5000,
100, 40, 'test', 'Monarchy', 'WO');

INSERT INTO countrylanguage (countrycode, language, isofficial,
percentage) VALUES ('WON', 'German', TRUE, 60.5
);

INSERT INTO countrylanguage (countrycode, language, isofficial,
percentage) VALUES ('WON', 'Italian', TRUE, 20.0);

INSERT INTO countrylanguage (countrycode, language, isofficial,
percentage) VALUES ('WON', 'Other', TRUE, 19.5);


--

Both queries should r
eturn the same relation with 3 tuples

SELECT * FROM country_v WHERE c_code = 'WON';

SELECT * FROM country_mv WHERE c_code = 'WON';


--

Test UPDATE triggers
--------------------------------------------

UPDATE city SET name = 'Entenhausen' WHERE id = 5000;

U
PDATE country SET name = 'Mittelerde' WHERE capital = 5000;

UPDATE countrylanguage SET percentage = 20 WHERE countrycode = 'WON'
AND language = 'Other';

UPDATE countrylanguage SET percentage = 19.5 WHERE countrycode =
'WON' AND language = 'Italian';


--

Bo
th queries should return again the same relation

SELECT * FROM country_v WHERE c_code = 'WON';

SELECT * FROM country_mv WHERE c_code = 'WON';


--

Test DELETE triggers
--------------------------------------------

DELETE FROM countrylanguage WHERE countrycod
e = 'WON' AND language =
'German';

DELETE FROM countrylanguage WHERE countrycode = 'WON' AND language =
'Italian';

DELETE FROM countrylanguage WHERE countrycode = 'WON' AND language =
Materialized Views in PostgreSQL



R. Guadagnini


31

'Other';

DELETE FROM country WHERE code = 'WON';

DELETE FROM city WHERE
id = 5000;


--

Both queries should return the empty relation

SELECT * FROM country_v WHERE c_code = 'WON';

SELECT * FROM country_mv WHERE c_code = 'WON';