Re: odd behavior in materialized view

Fujii Masao <masao.fujii@gmail.com> wrote:
> On Tue, Mar 5, 2013 at 7:36 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
>> Fujii Masao <masao.fujii@gmail.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