Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4335155

Browse files
committed
Make materialized views participate in predicate locking
Matviews have been discarded from needing predicate locks since3bf3ab8and their introduction. At this point, there was no concurrent flavorof REFRESH yet, hence there was no meaning in having materialized viewslook at read/write conflicts with concurrent transactions usingthemselves the serializable isolation level because they could only berefreshed with an access exclusive lock. CONCURRENTLY, on the contrary,allows reads and writes during a refresh as it holds a share updateexclusive lock.Some isolation tests are added to show the effect of the change, with acombination of one table and a matview based on it, using a mix ofREFRESH CONCURRENTLY and read/write queries.This could arguably be considered as a bug, but as it is a subtlebehavior change potentially impacting applications no backpatch isdone.Author: Yugo NagataReviewed-by: Richard Guo, Dilip Kumar, Michael PaquierDiscussion:https://postgr.es/m/20220726164434.42d4e33911b4b4fcf751c4e7@sraoss.co.jp
1 parentd5515ca commit4335155

File tree

4 files changed

+175
-3
lines changed

4 files changed

+175
-3
lines changed

‎src/backend/storage/lmgr/predicate.c

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -490,14 +490,13 @@ static void ReleasePredicateLocksLocal(void);
490490

491491
/*
492492
* Does this relation participate in predicate locking? Temporary and system
493-
* relations are exempt, as are materialized views.
493+
* relations are exempt.
494494
*/
495495
staticinlinebool
496496
PredicateLockingNeededForRelation(Relationrelation)
497497
{
498498
return !(relation->rd_id<FirstUnpinnedObjectId||
499-
RelationUsesLocalBuffers(relation)||
500-
relation->rd_rel->relkind==RELKIND_MATVIEW);
499+
RelationUsesLocalBuffers(relation));
501500
}
502501

503502
/*
Lines changed: 121 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,121 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: s1_begin s2_begin s1_refresh s2_read s2_insert s1_commit s2_commit
4+
step s1_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
5+
step s2_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
6+
step s1_refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
7+
step s2_read: SELECT max(date) FROM order_summary;
8+
max
9+
----------
10+
04-01-2022
11+
(1 row)
12+
13+
step s2_insert: INSERT INTO orders VALUES ('2022-04-02', 'orange', 15);
14+
step s1_commit: COMMIT;
15+
step s2_commit: COMMIT;
16+
ERROR: could not serialize access due to read/write dependencies among transactions
17+
18+
starting permutation: s1_begin s2_begin s1_refresh s2_read s2_update s1_commit s2_commit
19+
step s1_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
20+
step s2_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
21+
step s1_refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
22+
step s2_read: SELECT max(date) FROM order_summary;
23+
max
24+
----------
25+
04-01-2022
26+
(1 row)
27+
28+
step s2_update: UPDATE orders SET num = num + 1;
29+
step s1_commit: COMMIT;
30+
step s2_commit: COMMIT;
31+
ERROR: could not serialize access due to read/write dependencies among transactions
32+
33+
starting permutation: s1_begin s2_begin s2_read s1_refresh s2_insert s1_commit s2_commit
34+
step s1_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
35+
step s2_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
36+
step s2_read: SELECT max(date) FROM order_summary;
37+
max
38+
----------
39+
04-01-2022
40+
(1 row)
41+
42+
step s1_refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
43+
step s2_insert: INSERT INTO orders VALUES ('2022-04-02', 'orange', 15);
44+
step s1_commit: COMMIT;
45+
step s2_commit: COMMIT;
46+
ERROR: could not serialize access due to read/write dependencies among transactions
47+
48+
starting permutation: s1_begin s2_begin s2_read s1_refresh s2_update s1_commit s2_commit
49+
step s1_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
50+
step s2_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
51+
step s2_read: SELECT max(date) FROM order_summary;
52+
max
53+
----------
54+
04-01-2022
55+
(1 row)
56+
57+
step s1_refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
58+
step s2_update: UPDATE orders SET num = num + 1;
59+
step s1_commit: COMMIT;
60+
step s2_commit: COMMIT;
61+
ERROR: could not serialize access due to read/write dependencies among transactions
62+
63+
starting permutation: s1_begin s2_begin s2_read s2_insert s1_refresh s1_commit s2_commit
64+
step s1_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
65+
step s2_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
66+
step s2_read: SELECT max(date) FROM order_summary;
67+
max
68+
----------
69+
04-01-2022
70+
(1 row)
71+
72+
step s2_insert: INSERT INTO orders VALUES ('2022-04-02', 'orange', 15);
73+
step s1_refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
74+
step s1_commit: COMMIT;
75+
step s2_commit: COMMIT;
76+
ERROR: could not serialize access due to read/write dependencies among transactions
77+
78+
starting permutation: s1_begin s2_begin s2_read s2_update s1_refresh s1_commit s2_commit
79+
step s1_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
80+
step s2_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
81+
step s2_read: SELECT max(date) FROM order_summary;
82+
max
83+
----------
84+
04-01-2022
85+
(1 row)
86+
87+
step s2_update: UPDATE orders SET num = num + 1;
88+
step s1_refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
89+
step s1_commit: COMMIT;
90+
step s2_commit: COMMIT;
91+
ERROR: could not serialize access due to read/write dependencies among transactions
92+
93+
starting permutation: s1_begin s2_begin s1_refresh s2_insert s2_read s1_commit s2_commit
94+
step s1_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
95+
step s2_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
96+
step s1_refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
97+
step s2_insert: INSERT INTO orders VALUES ('2022-04-02', 'orange', 15);
98+
step s2_read: SELECT max(date) FROM order_summary;
99+
max
100+
----------
101+
04-01-2022
102+
(1 row)
103+
104+
step s1_commit: COMMIT;
105+
step s2_commit: COMMIT;
106+
ERROR: could not serialize access due to read/write dependencies among transactions
107+
108+
starting permutation: s1_begin s2_begin s1_refresh s2_update s2_read s1_commit s2_commit
109+
step s1_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
110+
step s2_begin: BEGIN ISOLATION LEVEL SERIALIZABLE;
111+
step s1_refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
112+
step s2_update: UPDATE orders SET num = num + 1;
113+
step s2_read: SELECT max(date) FROM order_summary;
114+
max
115+
----------
116+
04-01-2022
117+
(1 row)
118+
119+
step s1_commit: COMMIT;
120+
step s2_commit: COMMIT;
121+
ERROR: could not serialize access due to read/write dependencies among transactions

‎src/test/isolation/isolation_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -108,3 +108,4 @@ test: cluster-conflict-partition
108108
test: truncate-conflict
109109
test: serializable-parallel
110110
test: serializable-parallel-2
111+
test: matview-write-skew
Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,51 @@
1+
# Test write skew with a materialized view.
2+
#
3+
# This test uses two serializable transactions: one that refreshes a
4+
# materialized view containing a summary of some order information, and
5+
# one that looks at the materialized view while doing writes on its
6+
# parent relation.
7+
#
8+
# Any overlap between the transactions should cause a serialization failure.
9+
10+
setup
11+
{
12+
CREATETABLEorders (datedate,itemtext,numint);
13+
INSERTINTOordersVALUES ('2022-04-01','apple',10), ('2022-04-01','banana',20);
14+
15+
CREATEMATERIALIZEDVIEWorder_summaryAS
16+
SELECTdate,item,sum(num)FROMordersGROUPBYdate,item;
17+
CREATEUNIQUEINDEXONorder_summary(date,item);
18+
--Createadiffbetweenthesummarytableandtheparentorders.
19+
INSERTINTOordersVALUES ('2022-04-02','apple',20);
20+
}
21+
22+
teardown
23+
{
24+
DROPMATERIALIZEDVIEWorder_summary;
25+
DROPTABLEorders;
26+
}
27+
28+
sessions1
29+
steps1_begin {BEGINISOLATIONLEVELSERIALIZABLE; }
30+
steps1_refresh {REFRESHMATERIALIZEDVIEWCONCURRENTLYorder_summary; }
31+
steps1_commit {COMMIT; }
32+
33+
sessions2
34+
steps2_begin {BEGINISOLATIONLEVELSERIALIZABLE; }
35+
steps2_read {SELECTmax(date)FROMorder_summary; }
36+
steps2_insert {INSERTINTOordersVALUES ('2022-04-02','orange',15); }
37+
steps2_update {UPDATEordersSETnum=num+1; }
38+
steps2_commit {COMMIT; }
39+
40+
# refresh -> read -> write
41+
permutation"s1_begin""s2_begin""s1_refresh""s2_read""s2_insert""s1_commit""s2_commit"
42+
permutation"s1_begin""s2_begin""s1_refresh""s2_read""s2_update""s1_commit""s2_commit"
43+
# read -> refresh -> write
44+
permutation"s1_begin""s2_begin""s2_read""s1_refresh""s2_insert""s1_commit""s2_commit"
45+
permutation"s1_begin""s2_begin""s2_read""s1_refresh""s2_update""s1_commit""s2_commit"
46+
# read -> write -> refresh
47+
permutation"s1_begin""s2_begin""s2_read""s2_insert""s1_refresh""s1_commit""s2_commit"
48+
permutation"s1_begin""s2_begin""s2_read""s2_update""s1_refresh""s1_commit""s2_commit"
49+
# refresh -> write -> read
50+
permutation"s1_begin""s2_begin""s1_refresh""s2_insert""s2_read""s1_commit""s2_commit"
51+
permutation"s1_begin""s2_begin""s1_refresh""s2_update""s2_read""s1_commit""s2_commit"

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp