Thread: 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 MATERIALIZEDVIEW 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.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 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@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 ASSELECT * 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; blk0 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.fujii@gmail.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.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
Attachment
On Thu, Mar 7, 2013 at 8:21 AM, Kevin Grittner <kgrittn@ymail.com> wrote: > 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. 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.fujii@gmail.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@ymail.com> wrote: > Fujii Masao <masao.fujii@gmail.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
Attachment
Fujii Masao <masao.fujii@gmail.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@ymail.com> wrote: > Fujii Masao <masao.fujii@gmail.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
Attachment
On Fri, Mar 8, 2013 at 1:52 AM, Kevin Grittner <kgrittn@ymail.com> wrote: > Fujii Masao <masao.fujii@gmail.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.fujii@gmail.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