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

"j.rejda@konektel.cz" <j.rejda@konektel.cz> wrote:=0A=0A> -- simple table=
=0A> CREATE TABLE test (cislo integer);=0A> INSERT INTO test VALUES (10);=
=0A>=0A> -- immutable "long time" math func=0A> CREATE FUNCTION long_test(_=
n bigint)=0A>=A0=A0 RETURNS bigint AS=0A> $BODY$DECLARE=0A>=A0=A0 _result b=
igint :=3D 0;=0A>=A0=A0 _i bigint :=3D 2;=0A> BEGIN=0A>=A0=A0 _n :=3D !! _n=
;=0A>=A0=A0 WHILE _i < _n LOOP=0A>=A0=A0=A0=A0 IF _n % _i =3D 0 THEN=0A>=A0=
=A0=A0=A0=A0=A0 _result :=3D _result + 1;=0A>=A0=A0=A0=A0 END IF;=0A>=A0=A0=
=A0=A0 _i :=3D _i + 1;=0A>=A0=A0 END LOOP;=0A>=A0=A0 RETURN _result;=0A> EN=
D;$BODY$=0A>=A0=A0 LANGUAGE plpgsql IMMUTABLE STRICT=0A>=A0=A0 COST 100;=0A=
>=0A> -- this returns "immediately"=0A> CREATE MATERIALIZED VIEW test1 AS=
=0A>=A0=A0 SELECT long_test(cislo) FROM test WITH NO DATA;=0A>=0A> -- this =
returns "after some time" (as long as normal "SELECT=0A> long_test(10);"=0A=
> do)=0A> CREATE MATERIALIZED VIEW test2 AS=0A>=A0=A0 SELECT long_test(10) =
WITH NO DATA;=0A>=0A> -- it's ok or bug?=0A=0AIt's not a bug in the sense t=
hat it crashes or returns an incorrect=0Aresult, but it may be an opportuni=
ty for a performance enhancement.=0A=0AFWIW, CREATE TABLE AS has the same b=
ehavior.=A0 Just replace=0AMATERIALIZED VIEW in your examples with TABLE to=
 see it.=0A=0A--=0AKevin Grittner=0AEDB: http://www.enterprisedb.com=0AThe =
Enterprise PostgreSQL Company