Horton

Thread

Commits

  1. Make materialized views participate in predicate locking

  1. SI-read predicate locks on materialized views

    Yugo Nagata <nagata@sraoss.co.jp> — 2022-07-26T07:44:34Z

    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>

  2. Re: SI-read predicate locks on materialized views

    Richard Guo <guofenglinux@gmail.com> — 2022-07-26T10:00:57Z

    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

  3. Re: SI-read predicate locks on materialized views

    Dilip Kumar <dilipbalaut@gmail.com> — 2022-09-09T10:57:45Z

    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

  4. Re: SI-read predicate locks on materialized views

    Yugo Nagata <nagata@sraoss.co.jp> — 2022-09-30T01:12:13Z

    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>

  5. Re: SI-read predicate locks on materialized views

    Michael Paquier <michael@paquier.xyz> — 2022-10-13T08:02:06Z

    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

  6. Re: SI-read predicate locks on materialized views

    Yugo Nagata <nagata@sraoss.co.jp> — 2022-10-18T08:29:58Z

    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>

  7. Re: SI-read predicate locks on materialized views

    Michael Paquier <michael@paquier.xyz> — 2022-12-01T06:48:21Z

    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

  8. Re: SI-read predicate locks on materialized views

    Yugo Nagata <nagata@sraoss.co.jp> — 2022-12-01T07:53:11Z

    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>