Hi,
I propose to acquire SI-read predicate locks on materialized views
as the attached patch.
Currently, materialized views do not participate in predicate locking,
but I think this causes a serialization anomaly when `REFRESH
MATERIALIZED VIEW CONCURRENTLY` is used.
For example, supporse that there is a table "orders" which contains
order information and a materialized view "order_summary" which contains
summary of the order information.
CREATE TABLE orders (date date, item text, num int);
CREATE MATERIALIZED VIEW order_summary AS
SELECT date, item, sum(num) FROM orders GROUP BY date, item;
"order_summary" is refreshed once per day in the following transaction.
T1:
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
"orders" has a date column, and when a new item is inserted, the date
value is determined as the next day of the last date recorded in
"order_summary" as in the following transaction.
T2:
SELECT max(date) + 1 INTO today FROM order_summary;
INSERT INTO orders(date, item, num) VALUES (today, 'apple', 1);
If such two transactions run concurrently, a write skew anomaly occurs,
and the result of order_summary refreshed in T1 will not contain the
record inserted in T2.
On the other hand, if the materialized view participates in predicate
locking and the transaction isolation level is SELIALIZABLE, this
anomaly can be avoided; one of the transaction will be aborted and
suggested to be retried.
The problem doesn't occur when we use REFRESH MATERIALIZED VIEW
(not CONCURRENTLY) because it acquires the strongest lock and
any concurrent transactions are prevent from reading the materialized view.
I think this is the reason why materialized views didn't have to
participate in predicate locking. However, this is no longer the case
because now we support REFRESH ... CONCURRENTLY which refreshes the
materialized view using DELETE and INSERT and also allow to read it
from concurrent transactions. I think we can regard them as same as
DELETE, INSERT, and SELECT on regular tables and acquire predicate
locks on materialized views as well.
What do you think about it?
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>