Re: Materialized views WIP patch

On 16 January 2013 17:25, Thom Brown <thom(at)linux(dot)com> wrote:

> 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;
>

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.

--
Thom