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

Commitbf4052f

Browse files
committed
Don't reject ROW_MARK_REFERENCE rowmarks for materialized views.
We should allow this so that matviews can be referenced in UPDATE/DELETEstatements in READ COMMITTED isolation level. The requirement for thatis that a re-fetch by TID will see the same row version the query sawearlier, which is true of matviews, so there's no reason for therestriction. Per bug #9398.Michael Paquier, after a suggestion by me
1 parent0024a3a commitbf4052f

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
caseRELKIND_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
caseRELKIND_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
DROPTABLE v CASCADE;
189+
190+
-- make sure that matview rows can be referenced as source rows (bug #9398)
191+
CREATETABLEvASSELECT generate_series(1,10)AS a;
192+
CREATE MATERIALIZED VIEW mv_vASSELECT aFROM vWHERE a<=5;
193+
DELETEFROM vWHERE EXISTS (SELECT*FROM mv_vWHEREmv_v.a=v.a );
194+
SELECT*FROM v;
195+
SELECT*FROM mv_v;
196+
DROPTABLE v CASCADE;

0 commit comments

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