Re: odd behavior in materialized view

Hi,

When I accessed the materialized view in the standby server,
I got the following ERROR message. Looks odd to me. Is this a bug?

ERROR: materialized view "hogeview" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.

The procedure to reproduce this error message is:

In the master server:
CREATE TABLE hoge (i int);
INSERT INTO hoge VALUES (generate_series(1,100));
CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge;
DELETE FROM hoge;
REFRESH MATERIALIZED VIEW hogeview;
SELECT count(*) FROM hogeview;

In the standby server
SELECT count(*) FROM hogeview;

SELECT count(*) goes well in the master, and expectedly returns 0.
OTOH, in the standby, it emits the error message.

Regards,

--
Fujii Masao

Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> When I accessed the materialized view in the standby server,

> I got the following ERROR message. Looks odd to me. Is this a bug?
>
>    ERROR:  materialized view "hogeview" has not been populated
>    HINT:  Use the REFRESH MATERIALIZED VIEW command.
>
> The procedure to reproduce this error message is:
>
> In the master server:
>    CREATE TABLE hoge (i int);
>    INSERT INTO hoge VALUES (generate_series(1,100));
>    CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge;
>    DELETE FROM hoge;
>    REFRESH MATERIALIZED VIEW hogeview;
>    SELECT count(*) FROM hogeview;
>
> In the standby server
>    SELECT count(*) FROM hogeview;
>
> SELECT count(*) goes well in the master, and expectedly returns 0.
> OTOH, in the standby, it emits the error message.

Will investigate.

Thanks for testing!

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

On Tue, Mar 5, 2013 at 7:36 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>
>> When I accessed the materialized view in the standby server,
>
>> I got the following ERROR message. Looks odd to me. Is this a bug?
>>
>> ERROR: materialized view "hogeview" has not been populated
>> HINT: Use the REFRESH MATERIALIZED VIEW command.
>>
>> The procedure to reproduce this error message is:
>>
>> In the master server:
>> CREATE TABLE hoge (i int);
>> INSERT INTO hoge VALUES (generate_series(1,100));
>> CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge;
>> DELETE FROM hoge;
>> REFRESH MATERIALIZED VIEW hogeview;
>> SELECT count(*) FROM hogeview;
>>
>> In the standby server
>> SELECT count(*) FROM hogeview;
>>
>> SELECT count(*) goes well in the master, and expectedly returns 0.
>> OTOH, in the standby, it emits the error message.
>
> Will investigate.

Thanks!

And I found another problem. When I ran the following SQLs in the master,
PANIC error occurred in the standby.

CREATE TABLE hoge (i int);
INSERT INTO hoge VALUES (generate_series(1,100));
CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge;
VACUUM ANALYZE;

The PANIC error messages that I got in the standby are

WARNING: page 0 of relation base/12297/16387 is uninitialized
CONTEXT: xlog redo visible: rel 1663/12297/16387; blk 0
PANIC: WAL contains references to invalid pages
CONTEXT: xlog redo visible: rel 1663/12297/16387; blk 0

base/12297/16387 is the file of the materialized view 'hogeview'.

Regards,

--
Fujii Masao

Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> And I found another problem. When I ran the following SQLs in the
> master, PANIC error occurred in the standby.
>
>     CREATE TABLE hoge (i int);
>     INSERT INTO hoge VALUES (generate_series(1,100));
>     CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge;
>     VACUUM ANALYZE;
>
> The PANIC error messages that I got in the standby are
>
>     WARNING:  page 0 of relation base/12297/16387 is uninitialized
>     CONTEXT:  xlog redo visible: rel 1663/12297/16387; blk 0
>     PANIC:  WAL contains references to invalid pages
>     CONTEXT:  xlog redo visible: rel 1663/12297/16387; blk 0
>
> base/12297/16387 is the file of the materialized view 'hogeview'.

Yeah, that looks like it will be fixed by the fix for the first
problem.  The write of a first page without any rows to indicate
that it is a scannable empty relation must be WAL-logged.  I should
have something later today.

Thanks for spotting this.

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

Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Tue, Mar 5, 2013 at 7:36 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>
>>> When I accessed the materialized view in the standby server,
>>
>>> I got the following ERROR message. Looks odd to me. Is this a bug?
>>>
>>>     ERROR:  materialized view "hogeview" has not been populated
>>>     HINT:  Use the REFRESH MATERIALIZED VIEW command.
>>>
>>> The procedure to reproduce this error message is:
>>>
>>> In the master server:
>>>     CREATE TABLE hoge (i int);
>>>     INSERT INTO hoge VALUES (generate_series(1,100));
>>>     CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge;
>>>     DELETE FROM hoge;
>>>     REFRESH MATERIALIZED VIEW hogeview;
>>>     SELECT count(*) FROM hogeview;
>>>
>>> In the standby server
>>>     SELECT count(*) FROM hogeview;
>>>
>>> SELECT count(*) goes well in the master, and expectedly returns 0.
>>> OTOH, in the standby, it emits the error message.
>>
>> Will investigate.
>
> Thanks!
>
> And I found another problem. When I ran the following SQLs in the master,
> PANIC error occurred in the standby.
>
>     CREATE TABLE hoge (i int);
>     INSERT INTO hoge VALUES (generate_series(1,100));
>     CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge;
>     VACUUM ANALYZE;
>
> The PANIC error messages that I got in the standby are
>
>     WARNING:  page 0 of relation base/12297/16387 is uninitialized
>     CONTEXT:  xlog redo visible: rel 1663/12297/16387; blk 0
>     PANIC:  WAL contains references to invalid pages
>     CONTEXT:  xlog redo visible: rel 1663/12297/16387; blk 0
>
> base/12297/16387 is the file of the materialized view 'hogeview'.

I was able to replicate both bugs, and they both appear to be fixed
by the attached, which I have just pushed.

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

On Thu, Mar 7, 2013 at 8:21 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Tue, Mar 5, 2013 at 7:36 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>>
>>>> When I accessed the materialized view in the standby server,
>>>
>>>> I got the following ERROR message. Looks odd to me. Is this a bug?
>>>>
>>>> ERROR: materialized view "hogeview" has not been populated
>>>> HINT: Use the REFRESH MATERIALIZED VIEW command.
>>>>
>>>> The procedure to reproduce this error message is:
>>>>
>>>> In the master server:
>>>> CREATE TABLE hoge (i int);
>>>> INSERT INTO hoge VALUES (generate_series(1,100));
>>>> CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge;
>>>> DELETE FROM hoge;
>>>> REFRESH MATERIALIZED VIEW hogeview;
>>>> SELECT count(*) FROM hogeview;
>>>>
>>>> In the standby server
>>>> SELECT count(*) FROM hogeview;
>>>>
>>>> SELECT count(*) goes well in the master, and expectedly returns 0.
>>>> OTOH, in the standby, it emits the error message.
>>>
>>> Will investigate.
>>
>> Thanks!
>>
>> And I found another problem. When I ran the following SQLs in the master,
>> PANIC error occurred in the standby.
>>
>> CREATE TABLE hoge (i int);
>> INSERT INTO hoge VALUES (generate_series(1,100));
>> CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge;
>> VACUUM ANALYZE;
>>
>> The PANIC error messages that I got in the standby are
>>
>> WARNING: page 0 of relation base/12297/16387 is uninitialized
>> CONTEXT: xlog redo visible: rel 1663/12297/16387; blk 0
>> PANIC: WAL contains references to invalid pages
>> CONTEXT: xlog redo visible: rel 1663/12297/16387; blk 0
>>
>> base/12297/16387 is the file of the materialized view 'hogeview'.
>
> I was able to replicate both bugs, and they both appear to be fixed
> by the attached, which I have just pushed.

Thanks! I confirmed that the problem that I reported has disappeared in HEAD.

Unfortunately I found another odd behavior. When I accessed the MV
after VACUUM ANALYZE, I got the following error.

ERROR: materialized view "hogeview" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
STATEMENT: select * from hogeview where i < 10;

The test case to reproduce that is:

create table hoge (i int);
insert into hoge values (generate_series(1,100000));
create materialized view hogeview as select * from hoge where i % 2 = 0;
create index hogeviewidx on hogeview (i);
delete from hoge;
refresh materialized view hogeview;
select * from hogeview where i < 10;
vacuum analyze;
select * from hogeview where i < 10;

The last SELECT command caused the above error.

Regards,

--
Fujii Masao

Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> Thanks! I confirmed that the problem that I reported has
> disappeared in HEAD.
>
> Unfortunately I found another odd behavior. When I accessed the
> MV after VACUUM ANALYZE, I got the following error.
>
>     ERROR:  materialized view "hogeview" has not been populated
>     HINT:  Use the REFRESH MATERIALIZED VIEW command.
>     STATEMENT:  select * from hogeview where i < 10;
>
> The test case to reproduce that is:
>
> create table hoge (i int);
> insert into hoge values (generate_series(1,100000));
> create materialized view hogeview as select * from hoge where i % 2 = 0;
> create index hogeviewidx on hogeview (i);
> delete from hoge;
> refresh materialized view hogeview;
> select * from hogeview where i < 10;
> vacuum analyze;
> select * from hogeview where i < 10;
>
> The last SELECT command caused the above error.

Thanks.  Will fix.

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

On Fri, Mar 8, 2013 at 1:52 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>
>> Thanks! I confirmed that the problem that I reported has
>> disappeared in HEAD.
>>
>> Unfortunately I found another odd behavior. When I accessed the
>> MV after VACUUM ANALYZE, I got the following error.
>>
>> ERROR: materialized view "hogeview" has not been populated
>> HINT: Use the REFRESH MATERIALIZED VIEW command.
>> STATEMENT: select * from hogeview where i < 10;
>>
>> The test case to reproduce that is:
>>
>> create table hoge (i int);
>> insert into hoge values (generate_series(1,100000));
>> create materialized view hogeview as select * from hoge where i % 2 = 0;
>> create index hogeviewidx on hogeview (i);
>> delete from hoge;
>> refresh materialized view hogeview;
>> select * from hogeview where i < 10;
>> vacuum analyze;
>> select * from hogeview where i < 10;
>>
>> The last SELECT command caused the above error.
>
> Thanks. Will fix.

Thanks!

I found one typo in the document of MV. Please see the attached patch.

Regards,

--
Fujii Masao

Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> I found one typo in the document of MV. Please see the attached
> patch.

Pushed.  Thanks!

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

On Fri, Mar 8, 2013 at 2:42 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>
>> I found one typo in the document of MV. Please see the attached
>> patch.
>
> Pushed. Thanks!

Thanks!

I found that pg_dump always fails against 9.2 or before server because
of the MV patch.

$ pg_dump
pg_dump: [archiver (db)] query failed: pg_dump: [archiver (db)] query was:

Attached patch fixes this problem.

Regards,

--
Fujii Masao

On Fri, Mar 8, 2013 at 1:52 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>
>> Thanks! I confirmed that the problem that I reported has
>> disappeared in HEAD.
>>
>> Unfortunately I found another odd behavior. When I accessed the
>> MV after VACUUM ANALYZE, I got the following error.
>>
>> ERROR: materialized view "hogeview" has not been populated
>> HINT: Use the REFRESH MATERIALIZED VIEW command.
>> STATEMENT: select * from hogeview where i < 10;
>>
>> The test case to reproduce that is:
>>
>> create table hoge (i int);
>> insert into hoge values (generate_series(1,100000));
>> create materialized view hogeview as select * from hoge where i % 2 = 0;
>> create index hogeviewidx on hogeview (i);
>> delete from hoge;
>> refresh materialized view hogeview;
>> select * from hogeview where i < 10;
>> vacuum analyze;
>> select * from hogeview where i < 10;
>>
>> The last SELECT command caused the above error.
>
> Thanks. Will fix.

Ping? ISTM this problem has not been fixed in HEAD yet.

Regards,

--
Fujii Masao

Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> Ping? ISTM this problem has not been fixed in HEAD yet.

It's next on my list.  The other reports seemed more serious and
more likely to be contentious in terms of the best fix.

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