Re: Materialized views WIP patch

On 16 January 2013 17:25, Thom Brown <thom@linux.com> wrote:

On 16 January 2013 17:20, Kevin Grittner <kgrittn@mail.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;

A separate issue is with psql tab-completion:

postgres=# COMMENT ON MATERIALIZED VIEW ^IIS

This should be offering MV names instead of prematurely providing the "IS" keyword.