SI-read predicate locks on materialized views
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>
Attachments:
predicate_locks_on_matview.patchtext/x-diff; name=predicate_locks_on_matview.patchDownload+2-3
On Tue, Jul 26, 2022 at 3:44 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
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.
Indeed we have write skew anomaly here between the two transactions.
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 idea works for me.
Thanks
Richard
On Tue, Jul 26, 2022 at 3:31 PM Richard Guo <guofenglinux@gmail.com> wrote:
On Tue, Jul 26, 2022 at 3:44 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
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.
Yes we do have write skew anomaly. I think the patch looks fine to me.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
On Fri, 9 Sep 2022 16:27:45 +0530
Dilip Kumar <dilipbalaut@gmail.com> wrote:
On Tue, Jul 26, 2022 at 3:31 PM Richard Guo <guofenglinux@gmail.com> wrote:
On Tue, Jul 26, 2022 at 3:44 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
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.Yes we do have write skew anomaly. I think the patch looks fine to me.
Thank you for comment. Do you think it can be marked as Ready for Commiter?
Regards,
Yugo Nagata
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
--
Yugo NAGATA <nagata@sraoss.co.jp>
On Fri, Sep 30, 2022 at 10:12:13AM +0900, Yugo NAGATA wrote:
Thank you for comment. Do you think it can be marked as Ready for Commiter?
Matviews have been discarded from needing predicate locks since
3bf3ab8 and their introduction, where there was no concurrent flavor
of refresh yet. Shouldn't this patch have at least an isolation test
to show the difference in terms of read-write conflicts with some
serializable transactions and REFRESH CONCURRENTLY?
--
Michael
Hello Micheal-san,
On Thu, 13 Oct 2022 17:02:06 +0900
Michael Paquier <michael@paquier.xyz> wrote:
On Fri, Sep 30, 2022 at 10:12:13AM +0900, Yugo NAGATA wrote:
Thank you for comment. Do you think it can be marked as Ready for Commiter?
Matviews have been discarded from needing predicate locks since
3bf3ab8 and their introduction, where there was no concurrent flavor
of refresh yet. Shouldn't this patch have at least an isolation test
to show the difference in terms of read-write conflicts with some
serializable transactions and REFRESH CONCURRENTLY?
Thank you for your review. I agree that an isolation test is required.
The attached patch contains the test using the scenario as explained in
the previous post.
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
0001-SI-read-predicate-locking-on-materialized-views.patchtext/x-diff; name=0001-SI-read-predicate-locking-on-materialized-views.patchDownload+123-4
On Tue, Oct 18, 2022 at 05:29:58PM +0900, Yugo NAGATA wrote:
Thank you for your review. I agree that an isolation test is required.
The attached patch contains the test using the scenario as explained in
the previous post.
Cool, thanks. Sorry for my late reply here. I have put my head on
that for a few hours and could not see why we should not allow that.
So committed the change after a few tweaks to the tests with the use
of custom permutations, mainly.
While looking at all that, I have looked at the past threads like [1]/messages/by-id/1371225929.28496.YahooMailNeo@web162905.mail.bf1.yahoo.com -- Michael,
just to note that this has never been really mentioned.
[1]: /messages/by-id/1371225929.28496.YahooMailNeo@web162905.mail.bf1.yahoo.com -- Michael
--
Michael
On Thu, 1 Dec 2022 15:48:21 +0900
Michael Paquier <michael@paquier.xyz> wrote:
On Tue, Oct 18, 2022 at 05:29:58PM +0900, Yugo NAGATA wrote:
Thank you for your review. I agree that an isolation test is required.
The attached patch contains the test using the scenario as explained in
the previous post.Cool, thanks. Sorry for my late reply here. I have put my head on
that for a few hours and could not see why we should not allow that.
So committed the change after a few tweaks to the tests with the use
of custom permutations, mainly.
Thank!
While looking at all that, I have looked at the past threads like [1],
just to note that this has never been really mentioned.[1]: /messages/by-id/1371225929.28496.YahooMailNeo@web162905.mail.bf1.yahoo.com
--
Michael
--
Yugo NAGATA <nagata@sraoss.co.jp>