Thread: 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>
Attachment
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.
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
Attachment
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>
Attachment
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], just to note that this has never been really mentioned. [1]: https://www.postgresql.org/message-id/1371225929.28496.YahooMailNeo@web162905.mail.bf1.yahoo.com -- Michael
Attachment
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]: https://www.postgresql.org/message-id/1371225929.28496.YahooMailNeo@web162905.mail.bf1.yahoo.com > -- > Michael -- Yugo NAGATA <nagata@sraoss.co.jp>