"t.katsumata1...@gmail.com" <t.katsumata1...@gmail.com> wrote:
> I'm testing the Materialized View.
> When I've tried to create materialized view with specified
> column_name, I got an ERROR.
>
> example:
> - Creating original table
> CREATE TABLE t ( i int );
>
> - Creating materialized view with column_name
> CREATE MATERIALIZED VIEW mv_t(ii) AS SELECT * FROM t;
>
> And then, I got a bellow ERROR.
> ----
> ERROR: SELECT rule's target entry 1 has different column name from "ii"
> ----
>
> I did not get any ERROR with non materialized view.
> CREATE VIEW mv_t(ii) AS SELECT * FROM t;
>
> Is this a bug or restriction for Materialized View?
It's a bug. Will fix in the next 9.3 minor release.
Moving the discussion to the -hackers list to discuss the fix.
This bug was introduced in fb60e7296c2cf15195802b4596496b179bdc905a
based on this feedback:
http://www.postgresql.org/message-id/20600.1363022...@sss.pgh.pa.us
I picked the wrong response to that feedback. Attached is a patch
which fixes things along the alternative lines suggested. This
includes a regression test to ensure that this doesn't get broken
again.
If there are no objections I'll apply this within a few days.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
*** a/src/backend/rewrite/rewriteDefine.c
--- b/src/backend/rewrite/rewriteDefine.c
***************
*** 44,50 ****
static void checkRuleResultList(List *targetList, TupleDesc resultDesc,
! bool isSelect);
static bool setRuleCheckAsUser_walker(Node *node, Oid *context);
static void setRuleCheckAsUser_Query(Query *qry, Oid userid);
--- 44,50 ----
static void checkRuleResultList(List *targetList, TupleDesc resultDesc,
! bool isSelect, bool isMatview);
static bool setRuleCheckAsUser_walker(Node *node, Oid *context);
static void setRuleCheckAsUser_Query(Query *qry, Oid userid);
***************
*** 355,361 **** DefineQueryRewrite(char *rulename,
*/
checkRuleResultList(query->targetList,
RelationGetDescr(event_relation),
! true);
/*
* ... there must not be another ON SELECT rule already ...
--- 355,363 ----
*/
checkRuleResultList(query->targetList,
RelationGetDescr(event_relation),
! true,
! event_relation->rd_rel->relkind ==
! RELKIND_MATVIEW);
/*
* ... there must not be another ON SELECT rule already ...
***************
*** 484,490 **** DefineQueryRewrite(char *rulename,
errmsg("RETURNING lists are not supported in non-INSTEAD rules")));
checkRuleResultList(query->returningList,
RelationGetDescr(event_relation),
! false);
}
}
--- 486,492 ----
errmsg("RETURNING lists are not supported in non-INSTEAD rules")));
checkRuleResultList(query->returningList,
RelationGetDescr(event_relation),
! false, false);
}
}
***************
*** 615,623 **** DefineQueryRewrite(char *rulename,
* The targetList might be either a SELECT targetlist, or a RETURNING list;
* isSelect tells which. (This is mostly used for choosing error messages,
* but also we don't enforce column name matching for RETURNING.)
*/
static void
! checkRuleResultList(List *targetList, TupleDesc resultDesc, bool isSelect)
{
ListCell *tllist;
int i;
--- 617,628 ----
* The targetList might be either a SELECT targetlist, or a RETURNING list;
* isSelect tells which. (This is mostly used for choosing error messages,
* but also we don't enforce column name matching for RETURNING.)
+ *
+ * We also don't enforce column name match for a materialized view.
*/
static void
! checkRuleResultList(List *targetList, TupleDesc resultDesc, bool isSelect,
! bool isMatview)
{
ListCell *tllist;
int i;
***************
*** 657,663 **** checkRuleResultList(List *targetList, TupleDesc resultDesc, bool isSelect)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot convert relation containing dropped columns to view")));
! if (isSelect && strcmp(tle->resname, attname) != 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("SELECT rule's target entry %d has different column name from \"%s\"", i, attname)));
--- 662,668 ----
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot convert relation containing dropped columns to view")));
! if (isSelect && !isMatview && strcmp(tle->resname, attname) != 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("SELECT rule's target entry %d has different column name from \"%s\"", i, attname)));
*** a/src/test/regress/expected/matview.out
--- b/src/test/regress/expected/matview.out
***************
*** 444,446 **** SELECT * FROM boxmv ORDER BY id;
--- 444,468 ----
DROP TABLE boxes CASCADE;
NOTICE: drop cascades to materialized view boxmv
+ -- make sure that column names are handled correctly
+ CREATE TABLE v (i int, j int);
+ CREATE MATERIALIZED VIEW mv_v (ii) AS SELECT i, j AS jj FROM v;
+ ALTER TABLE v RENAME COLUMN i TO x;
+ INSERT INTO v values (1, 2);
+ CREATE UNIQUE INDEX mv_v_ii ON mv_v (ii);
+ REFRESH MATERIALIZED VIEW mv_v;
+ REFRESH MATERIALIZED VIEW CONCURRENTLY mv_v;
+ SELECT * FROM v;
+ x | j
+ ---+---
+ 1 | 2
+ (1 row)
+
+ SELECT * FROM mv_v;
+ ii | jj
+ ----+----
+ 1 | 2
+ (1 row)
+
+ DROP TABLE v CASCADE;
+ NOTICE: drop cascades to materialized view mv_v
*** a/src/test/regress/sql/matview.sql
--- b/src/test/regress/sql/matview.sql
***************
*** 167,169 **** UPDATE boxes SET b = '(2,2),(1,1)' WHERE id = 2;
--- 167,181 ----
REFRESH MATERIALIZED VIEW CONCURRENTLY boxmv;
SELECT * FROM boxmv ORDER BY id;
DROP TABLE boxes CASCADE;
+
+ -- make sure that column names are handled correctly
+ CREATE TABLE v (i int, j int);
+ CREATE MATERIALIZED VIEW mv_v (ii) AS SELECT i, j AS jj FROM v;
+ ALTER TABLE v RENAME COLUMN i TO x;
+ INSERT INTO v values (1, 2);
+ CREATE UNIQUE INDEX mv_v_ii ON mv_v (ii);
+ REFRESH MATERIALIZED VIEW mv_v;
+ REFRESH MATERIALIZED VIEW CONCURRENTLY mv_v;
+ SELECT * FROM v;
+ SELECT * FROM mv_v;
+ DROP TABLE v CASCADE;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
