From 3973034e6dc599de2203ed812f783a57b63dce5c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 6 Mar 2014 11:37:04 -0500 Subject: [PATCH] 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 9240b44dc8..a9c8140581 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 c61232fc89..0dc574b75c 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 8b1e7349fb..e9af757d60 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; -- 2.40.0