postgres.git / summary / log / commit / refs

commit    3973034e6dc599de2203ed812f783a57b63dce5c
Author:   Tom Lane <tgl@sss.pgh.pa.us>
Date:     Thu Mar 06 16:37:04 2014 +0000
First released in: 9.4.0, 9.3.4

    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


 src/backend/executor/execMain.c       | 13 +++++++------
 src/test/regress/expected/matview.out | 26 ++++++++++++++++++++++++++
 src/test/regress/sql/matview.sql      |  8 ++++++++
 3 files changed, 41 insertions(+), 6 deletions(-)

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 9240b44dc8d..a9c8140581b 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1100,14 +1100,15 @@ CheckValidRowMarkRel(Relation rel, RowMarkType markType)
 							RelationGetRelationName(rel))));
 			break;
 		case RELKIND_MATVIEW:
-			/* Should not get here */
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot lock rows in materialized view \"%s\"",
-							RelationGetRelationName(rel))));
+			/* Allow referencing a matview, but not actual locking clauses */
+			if (markType != ROW_MARK_REFERENCE)
+				ereport(ERROR,
+						(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					   errmsg("cannot lock rows in materialized view \"%s\"",
+							  RelationGetRelationName(rel))));
 			break;
 		case RELKIND_FOREIGN_TABLE:
-			/* Should not get here */
+			/* Should not get here; planner should have used ROW_MARK_COPY */
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
 					 errmsg("cannot lock rows in foreign table \"%s\"",
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index c61232fc891..0dc574b75cf 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -412,3 +412,29 @@ SELECT * FROM mv_v;
 
 DROP TABLE v CASCADE;
 NOTICE:  drop cascades to materialized view mv_v
+-- make sure that matview rows can be referenced as source rows (bug #9398)
+CREATE TABLE v AS SELECT generate_series(1,10) AS a;
+CREATE MATERIALIZED VIEW mv_v AS SELECT a FROM v WHERE a <= 5;
+DELETE FROM v WHERE EXISTS ( SELECT * FROM mv_v WHERE mv_v.a = v.a );
+SELECT * FROM v;
+ a  
+----
+  6
+  7
+  8
+  9
+ 10
+(5 rows)
+
+SELECT * FROM mv_v;
+ a 
+---
+ 1
+ 2
+ 3
+ 4
+ 5
+(5 rows)
+
+DROP TABLE v CASCADE;
+NOTICE:  drop cascades to materialized view mv_v
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 8b1e7349fb2..e9af757d609 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -141,3 +141,11 @@ REFRESH MATERIALIZED VIEW mv_v;
 SELECT * FROM v;
 SELECT * FROM mv_v;
 DROP TABLE v CASCADE;
+
+-- make sure that matview rows can be referenced as source rows (bug #9398)
+CREATE TABLE v AS SELECT generate_series(1,10) AS a;
+CREATE MATERIALIZED VIEW mv_v AS SELECT a FROM v WHERE a <= 5;
+DELETE FROM v WHERE EXISTS ( SELECT * FROM mv_v WHERE mv_v.a = v.a );
+SELECT * FROM v;
+SELECT * FROM mv_v;
+DROP TABLE v CASCADE;


[parent: 13ea43ab8387]