Skip site navigation (1) Skip section navigation (2)

Re: Materialized views WIP patch

From: Thom Brown <thom(at)linux(dot)com>
To: Kevin Grittner <kgrittn(at)mail(dot)com>
Cc: Marko Tiikkaja <pgmail(at)joh(dot)to>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Materialized views WIP patch
Date: 2013-01-16 17:25:29
Message-ID: CAA-aLv7Zn62Y5hOvHW0V1a=G5Vp_gGCx789tFntrCOC+HgW7ew@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 16 January 2013 17:20, Kevin Grittner <kgrittn(at)mail(dot)com> wrote:

> Thom Brown wrote:
>
> > Some weirdness:
> >
> > postgres=# CREATE VIEW v_test2 AS SELECT 1 moo;
> > CREATE VIEW
> > postgres=# CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM
> > v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2;
> > SELECT 2
> > postgres=# \d+ mv_test2
> >  Materialized view "public.mv_test2"
> >  Column | Type | Modifiers | Storage | Stats target | Description
> > ----------+---------+-----------+---------+--------------+-------------
> >  moo | integer | | plain | |
> >  ?column? | integer | | plain | |
> > View definition:
> >  SELECT "*SELECT* 1".moo, "*SELECT* 1"."?column?";
>
> You are very good at coming up with these, Thom!
>
> Will investigate.
>
> Can you confirm that *selecting* from the MV works as you would
> expect; it is just the presentation in \d+ that's a problem?
>

Yes, nothing wrong with using the MV, or refreshing it:

postgres=# TABLE mv_test2;
 moo | ?column?
-----+----------
   1 |        2
   1 |        3
(2 rows)

postgres=# SELECT * FROM mv_test2;
 moo | ?column?
-----+----------
   1 |        2
   1 |        3
(2 rows)

postgres=# REFRESH MATERIALIZED VIEW mv_test2;
REFRESH MATERIALIZED VIEW

But a pg_dump of the MV has the same issue as the view definition:

--
-- Name: mv_test2; Type: MATERIALIZED VIEW; Schema: public; Owner: thom;
Tablespace:
--

CREATE MATERIALIZED VIEW mv_test2 (
    moo,
    "?column?"
) AS
    SELECT "*SELECT* 1".moo, "*SELECT* 1"."?column?"
  WITH NO DATA;

-- 
Thom

In response to

Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2013-01-16 17:29:16
Subject: Re: Parallel query execution
Previous:From: Kevin GrittnerDate: 2013-01-16 17:20:50
Subject: Re: Materialized views WIP patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group