8000 Don't reject ROW_MARK_REFERENCE rowmarks for materialized views. · postgrespro/postgres_cluster@bf4052f · GitHub
[go: up one dir, main page]
Skip to content

Commit bf4052f

Browse files
tglsfdctglsfdc
committed
Don't reject ROW_MARK_REFERENCE rowmarks for materialized views.
We should allow this so that matviews can be referenced in UPDATE/DELETE statements in READ COMMITTED isolation level. The requirement for that is that a re-fetch by TID will see the same row version the query saw earlier, which is true of matviews, so there's no reason for the restriction. Per bug #9398. Michael Paquier, after a suggestion by me
1 parent 0024a3a commit bf4052f

File tree

3 files changed

+41
-6
lines changed

3 files changed

+41
-6
lines changed

‎src/backend/executor/execMain.c‎

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1102,14 +1102,15 @@ CheckValidRowMarkRel(Relation rel, RowMarkType markType)
11021102
RelationGetRelationName(rel))));
11031103
break;
11041104
case RELKIND_MATVIEW:
1105-
/* Should not get here */
1106-
ereport(ERROR,
1107-
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1108-
errmsg("cannot lock rows in materialized view \"%s\"",
1109-
RelationGetRelationName(rel))));
1105+
/* Allow referencing a matview, but not actual locking clauses */
1106+
if (markType != ROW_MARK_REFERENCE)
1107+
ereport(ERROR,
1108+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1109+
errmsg("cannot lock rows in materialized view \"%s\"",
1110+
RelationGetRelationName(rel))));
11101111
break;
11111112
case RELKIND_FOREIGN_TABLE:
1112-
/* Should not get here */
1113+
/* Should not get here; planner should have used ROW_MARK_COPY */
11131114
ereport(ERROR,
11141115
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
11151116
errmsg("cannot lock rows in foreign table \"%s\"",

‎src/test/regress/expected/matview.out‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -476,3 +476,29 @@ SELECT * FROM mv_v;
476476

477477
DROP TABLE v CASCADE;
478478
NOTICE: drop cascades to materialized view mv_v
479+
-- make sure that matview rows can be referenced as source rows (bug #9398)
480+
CREATE TABLE v AS SELECT generate_series(1,10) AS a;
481+
CREATE MATERIALIZED VIEW mv_v AS SELECT a FROM v WHERE a <= 5;
482+
DELETE FROM v WHERE EXISTS ( SELECT * FROM mv_v WHERE mv_v.a = v.a );
483+
SELECT * FROM v;
484+
a
485+
----
486+
6
487+
7
488+
8
489+
9
490+
10
491+
(5 rows)
492+
493+
SELECT * FROM mv_v;
494+
a
495+
---
496+
1
497+
2
498+
3
499+
4
500+
5
501+
(5 rows)
502+
503+
DROP TABLE v CASCADE;
504+
NOTICE: drop cascades to materialized view mv_v

‎src/test/regress/sql/matview.sql‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -186,3 +186,11 @@ REFRESH MATERIALIZED VIEW CONCURRENTLY mv_v;
186186
SELECT * FROM v;
187187
SELECT * FROM mv_v;
188188
DROP TABLE v CASCADE;
189+
190+
-- make sure that matview rows can be referenced as source rows (bug #9398)
191+
CREATE TABLE v AS SELECT generate_series(1,10) AS a;
192+
CREATE MATERIALIZED VIEW mv_v AS SELECT a FROM v WHERE a <= 5;
193+
DELETE FROM v WHERE EXISTS ( SELECT * FROM mv_v WHERE mv_v.a = v.a );
194+
SELECT * FROM v;
195+
SELECT * FROM mv_v;
196+
DROP TABLE v CASCADE;

0 commit comments

Comments
 (0)
0