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

"j(dot)rejda(at)konektel(dot)cz" <j(dot)rejda(at)konektel(dot)cz> wrote:

> -- simple table
> CREATE TABLE test (cislo integer);
> INSERT INTO test VALUES (10);
>
> -- immutable "long time" math func
> CREATE FUNCTION long_test(_n bigint)
>   RETURNS bigint AS
> $BODY$DECLARE
>   _result bigint := 0;
>   _i bigint := 2;
> BEGIN
>   _n := !! _n;
>   WHILE _i < _n LOOP
>     IF _n % _i = 0 THEN
>       _result := _result + 1;
>     END IF;
>     _i := _i + 1;
>   END LOOP;
>   RETURN _result;
> END;$BODY$
>   LANGUAGE plpgsql IMMUTABLE STRICT
>   COST 100;
>
> -- this returns "immediately"
> CREATE MATERIALIZED VIEW test1 AS
>   SELECT long_test(cislo) FROM test WITH NO DATA;
>
> -- this returns "after some time" (as long as normal "SELECT
> long_test(10);"
> do)
> CREATE MATERIALIZED VIEW test2 AS
>   SELECT long_test(10) WITH NO DATA;
>
> -- 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.

FWIW, CREATE TABLE AS has the same behavior.  Just replace
MATERIALIZED VIEW in your examples with TABLE to see it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company