Re: BUG #8606: Materialized View WITH NO DATA bug

Yes, that's what I had in mind ... I expected "CREATE MATERIALIZED VIEW =
...
WITH NO DATA" returns almost immediately and "REFRESH MATERIALIZED VIEW =
..."
will take a while. But when you use a query that can probably be =
evaluated
immediately, "CREATE MATERIALIZED VIEW ... WITH NO DATA" apparently do =
this
query (which spends enough time), but no data is populated. The same =
time it
takes with "REFRESH MATERIALIZED VIEW ..." to populate data. From my
perspective, this is wrong and should behave the same for any query, ie.
"CREATE MATERIALIZED VIEW ... WITH NO DATA" would not solve the query.

JR

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Wednesday, November 20, 2013 4:50 AM
To: Kevin Grittner
Cc: j.rejda@konektel.cz; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #8606: Materialized View WITH NO DATA bug

Kevin Grittner <kgrittn@ymail.com> writes:
> "j.rejda@konektel.cz" <j.rejda@konektel.cz> wrote:
>> -- this returns "after some time" (as long as normal "SELECT
>> long_test(10);"
>> do)
>> CREATE MATERIALIZED VIEW test2 AS
>> =A0=A0 SELECT long_test(10) WITH NO DATA;
>>=20
>> -- it's ok or bug?

> It's not a bug in the sense that it crashes or returns an incorrect
> result, but it may be an opportunity for a performance enhancement.

Meh.  I'm not sure this can be considered to be a problem.  One of
the implications of marking a function immutable is that it has no
side-effects worthy of concern; otherwise, it wouldn't be safe for
the planner to constant-fold it, which is pretty much the whole point
of immutability.  But if you're concerned about how long it takes to
evaluate, that's a kind of side effect, IMHO.  Maybe it'd be better
if you marked the function VOLATILE.

But having said that, it seems that this complaint implies that
CREATE MATERIALIZED VIEW ... WITH NO DATA still runs the query
through a planning cycle.  Why is that?  I can't immediately see
any benefit to doing so.

            regards, tom lane