Hello Micheal-san,
On Thu, 13 Oct 2022 17:02:06 +0900
Michael Paquier <mich...@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 <nag...@sraoss.co.jp>
>From b60a53a945283de4b068e1bc7aaafec26dd8f4da Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nag...@sraoss.co.jp>
Date: Tue, 18 Oct 2022 17:15:33 +0900
Subject: [PATCH] SI-read predicate locking on materialized views
Matviews have been discarded from needing predicate locks since
3bf3ab8 and their introduction, where there was no concurrent flavor
of refresh yet.
---
src/backend/storage/lmgr/predicate.c | 5 +-
.../isolation/expected/matview-write-skew.out | 77 +++++++++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../isolation/specs/matview-write-skew.spec | 43 +++++++++++
4 files changed, 123 insertions(+), 3 deletions(-)
create mode 100644 src/test/isolation/expected/matview-write-skew.out
create mode 100644 src/test/isolation/specs/matview-write-skew.spec
diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c
index e8120174d6..df1c0d72e9 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -490,14 +490,13 @@ static void ReleasePredicateLocksLocal(void);
/*
* Does this relation participate in predicate locking? Temporary and system
- * relations are exempt, as are materialized views.
+ * relations are exempt.
*/
static inline bool
PredicateLockingNeededForRelation(Relation relation)
{
return !(relation->rd_id < FirstUnpinnedObjectId ||
- RelationUsesLocalBuffers(relation) ||
- relation->rd_rel->relkind == RELKIND_MATVIEW);
+ RelationUsesLocalBuffers(relation));
}
/*
diff --git a/src/test/isolation/expected/matview-write-skew.out b/src/test/isolation/expected/matview-write-skew.out
new file mode 100644
index 0000000000..c1f8b3f5ea
--- /dev/null
+++ b/src/test/isolation/expected/matview-write-skew.out
@@ -0,0 +1,77 @@
+Parsed test spec with 2 sessions
+
+starting permutation: rxwy1 c1 rywx2 c2
+step rxwy1: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
+step c1: COMMIT;
+step rywx2:
+ DO $$DECLARE today date;
+ BEGIN
+ SELECT INTO today max(date) + 1 FROM order_summary;
+ INSERT INTO orders VALUES (today, 'banana', 10);
+ END$$;
+
+step c2: COMMIT;
+
+starting permutation: rxwy1 rywx2 c1 c2
+step rxwy1: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
+step rywx2:
+ DO $$DECLARE today date;
+ BEGIN
+ SELECT INTO today max(date) + 1 FROM order_summary;
+ INSERT INTO orders VALUES (today, 'banana', 10);
+ END$$;
+
+step c1: COMMIT;
+step c2: COMMIT;
+ERROR: could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxwy1 rywx2 c2 c1
+step rxwy1: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
+step rywx2:
+ DO $$DECLARE today date;
+ BEGIN
+ SELECT INTO today max(date) + 1 FROM order_summary;
+ INSERT INTO orders VALUES (today, 'banana', 10);
+ END$$;
+
+step c2: COMMIT;
+step c1: COMMIT;
+ERROR: could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rywx2 rxwy1 c1 c2
+step rywx2:
+ DO $$DECLARE today date;
+ BEGIN
+ SELECT INTO today max(date) + 1 FROM order_summary;
+ INSERT INTO orders VALUES (today, 'banana', 10);
+ END$$;
+
+step rxwy1: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
+step c1: COMMIT;
+step c2: COMMIT;
+ERROR: could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rywx2 rxwy1 c2 c1
+step rywx2:
+ DO $$DECLARE today date;
+ BEGIN
+ SELECT INTO today max(date) + 1 FROM order_summary;
+ INSERT INTO orders VALUES (today, 'banana', 10);
+ END$$;
+
+step rxwy1: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
+step c2: COMMIT;
+step c1: COMMIT;
+ERROR: could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rywx2 c2 rxwy1 c1
+step rywx2:
+ DO $$DECLARE today date;
+ BEGIN
+ SELECT INTO today max(date) + 1 FROM order_summary;
+ INSERT INTO orders VALUES (today, 'banana', 10);
+ END$$;
+
+step c2: COMMIT;
+step rxwy1: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
+step c1: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 5413a59a80..c11dc9a420 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -108,3 +108,4 @@ test: cluster-conflict-partition
test: truncate-conflict
test: serializable-parallel
test: serializable-parallel-2
+test: matview-write-skew
diff --git a/src/test/isolation/specs/matview-write-skew.spec b/src/test/isolation/specs/matview-write-skew.spec
new file mode 100644
index 0000000000..4437d00549
--- /dev/null
+++ b/src/test/isolation/specs/matview-write-skew.spec
@@ -0,0 +1,43 @@
+# Test write skew with a materialized view.
+#
+# This test has two serializable transactions: one which refreshes a
+# materialized view containing summary of the order information
+# (suppose that is performed once per day), and one which generates
+# an order whose record date is determined by referring to the last
+# One must be rolled back to prevent the write skew anomaly.
+#
+# Any overlap between the transactions must cause a serialization failure.
+
+setup
+{
+ CREATE TABLE orders (date date, item text, num int);
+ INSERT INTO orders VALUES ('2022-04-01', 'apple', 10), ('2022-04-01', 'banana', 20);
+
+ CREATE MATERIALIZED VIEW order_summary AS
+ SELECT date, item, sum(num) FROM orders GROUP BY date, item;;
+ CREATE UNIQUE INDEX ON order_summary(date, item);
+
+ INSERT INTO orders VALUES ('2022-04-02', 'apple', 20);
+}
+
+teardown
+{
+ DROP MATERIALIZED VIEW order_summary;
+ DROP TABLE orders;
+}
+
+session s1
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step rxwy1 { REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary; }
+step c1 { COMMIT; }
+
+session s2
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step rywx2 {
+ DO $$DECLARE today date;
+ BEGIN
+ SELECT INTO today max(date) + 1 FROM order_summary;
+ INSERT INTO orders VALUES (today, 'banana', 10);
+ END$$;
+}
+step c2 { COMMIT; }
--
2.25.1
