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

The following bug has been logged on the website:

Bug reference:      8606
Logged by:          Jaroslav Rejda
Email address:      j.rejda@konektel.cz
PostgreSQL version: 9.3.1
Operating system:   MS Windows XP SP3
Description:

-- Hi,
-- please, check this example:


-- 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?
-- Thanks ... JR

"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

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

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

Tom Lane <tgl@sss.pgh.pa.us> wrote:=0A=0A> But having said that, it seems t=
hat this complaint implies that=0A> CREATE MATERIALIZED VIEW ... WITH NO DA=
TA still runs the query=0A> through a planning cycle.=A0 Why is that?=A0 I =
can't immediately see=0A> any benefit to doing so.=0A=0AWell, to be honest,=
 it's because CREATE TABLE AS ... WITH NO DATA=0Adoes so, and I figured tha=
t if it was needed there I didn't see why=0ACREATE MATERIALIZED VIEW AS ...=
 WITH NO DATA would be any=0Adifferent.=A0 I'm shameless in copying what se=
ems to be working.=0A=0A--=0AKevin Grittner=0AEDB: http://www.enterprisedb.=
com=0AThe Enterprise PostgreSQL Company

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

Kevin Grittner <kgrittn@ymail.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> Well, to be honest, it's because CREATE TABLE AS ... WITH NO DATA
> does so, and I figured that if it was needed there I didn't see why
> CREATE MATERIALIZED VIEW AS ... WITH NO DATA would be any
> different.  I'm shameless in copying what seems to be working.

I looked at that a bit.  The reason we have to plan, in the current code,
is that we create the target table using column type info from the output
tupdesc supplied by the executor; so we have to have a plan to fire up the
executor on, even if we aren't going to fetch any data from the plan.

It's conceivable that we could extract result type info directly from
the original query (a la DefineVirtualRelation), but it'd be a significant
amount of work and it would mean that the WITH DATA and WITH NO DATA code
paths would be almost completely disjoint.  Worse, we'd still need
something pretty close to what we've got now to handle the CREATE TABLE
AS ... EXECUTE case, making that probably a third totally separate path.

So I'm thinking it's not worth the trouble to avoid a planning cycle
here.

            regards, tom lane