Skip site navigation (1) Skip section navigation (2)

Re: odd behavior in materialized view

From: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
To: Kevin Grittner <kgrittn(at)ymail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: odd behavior in materialized view
Date: 2013-03-07 16:18:45
Message-ID: CAHGQGwESOme9HUmUq_jTYi8j++qP2HoZxyqXR=37zuU8tHEOkw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Fujii MasaoDate: 2013-03-07 16:41:20
Subject: Re: Support for REINDEX CONCURRENTLY
Previous:From: Kevin GrittnerDate: 2013-03-07 15:55:29
Subject: Re: Materialized views WIP patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group