Thread: BUG #8542: Materialized View with another column_name does not work?

The following bug has been logged on the website:

Bug reference:      8542
Logged by:          Tomonari Katsumata
Email address:      t.katsumata1122@gmail.com
PostgreSQL version: 9.3.1
Operating system:   RHEL5 x86_64
Description:

Hi,


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?


regards,
-----------
Tomonari Katsumata

On Mon, Oct 21, 2013 at 6:41 PM,  <t.katsumata1122@gmail.com> wrote:
> - 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"
> Is this a bug or restriction for Materialized View?
Looks like a bug as documentation is clear here: "If column names are
not provided, they are taken from the output column names of the
query".
http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html
In your case you are providing the column names, so they should be
used for this matview.

Regards,
--
Michael

Hi Michael,

Thank you for replying!

I understand it seems a bug.

After some trying, I got a workaround for this.
CREATE MATERIALIZED VIEW mv_t AS SELECT i ii FROM t;

This seems working fine.
2013/10/21 Michael Paquier <michael.paquier@gmail.com>

> On Mon, Oct 21, 2013 at 6:41 PM,  <t.katsumata1122@gmail.com> wrote:
> > - 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"
> > Is this a bug or restriction for Materialized View?
> Looks like a bug as documentation is clear here: "If column names are
> not provided, they are taken from the output column names of the
> query".
> http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html
> In your case you are providing the column names, so they should be
> used for this matview.
>
> Regards,
> --
> Michael
>

"t.katsumata1122@gmail.com" <t.katsumata1122@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.1363022702@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