Re: [HACKERS] odd behavior in materialized view
Fujii Masao masao.fu...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
On Fri, Mar 8, 2013 at 1:52 AM, Kevin Grittner kgri...@ymail.com wrote: Fujii Masao masao.fu...@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,10)); 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
On Fri, Mar 8, 2013 at 2:42 AM, Kevin Grittner kgri...@ymail.com wrote: Fujii Masao masao.fu...@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 pg_dump_bugfix_matview.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
On Thu, Mar 7, 2013 at 8:21 AM, Kevin Grittner kgri...@ymail.com wrote: Fujii Masao masao.fu...@gmail.com wrote: On Tue, Mar 5, 2013 at 7:36 AM, Kevin Grittner kgri...@ymail.com wrote: Fujii Masao masao.fu...@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,10)); 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
Fujii Masao masao.fu...@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,10)); 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
On Fri, Mar 8, 2013 at 1:52 AM, Kevin Grittner kgri...@ymail.com wrote: Fujii Masao masao.fu...@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,10)); 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 typo.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
Fujii Masao masao.fu...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
Fujii Masao masao.fu...@gmail.com wrote: On Tue, Mar 5, 2013 at 7:36 AM, Kevin Grittner kgri...@ymail.com wrote: Fujii Masao masao.fu...@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*** a/src/backend/commands/matview.c --- b/src/backend/commands/matview.c *** *** 14,19 --- 14,20 */ #include postgres.h + #include access/heapam_xlog.h #include access/multixact.h #include access/relscan.h #include access/xact.h *** *** 68,77 SetRelationIsScannable(Relation relation) Assert(relation-rd_rel-relkind == RELKIND_MATVIEW); Assert(relation-rd_isscannable == false); - RelationOpenSmgr(relation); page = (Page) palloc(BLCKSZ); PageInit(page, BLCKSZ, 0); smgrextend(relation-rd_smgr, MAIN_FORKNUM, 0, (char *) page, true); pfree(page); smgrimmedsync(relation-rd_smgr, MAIN_FORKNUM); --- 69,83 Assert(relation-rd_rel-relkind == RELKIND_MATVIEW); Assert(relation-rd_isscannable == false); page = (Page) palloc(BLCKSZ); PageInit(page, BLCKSZ, 0); + + if (RelationNeedsWAL(relation)) + log_newpage((relation-rd_node), MAIN_FORKNUM, 0, page); + + RelationOpenSmgr(relation); smgrextend(relation-rd_smgr, MAIN_FORKNUM, 0, (char *) page, true); + pfree(page); smgrimmedsync(relation-rd_smgr, MAIN_FORKNUM); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
On Tue, Mar 5, 2013 at 7:36 AM, Kevin Grittner kgri...@ymail.com wrote: Fujii Masao masao.fu...@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'. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
Fujii Masao masao.fu...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] odd behavior in materialized view
Fujii Masao masao.fu...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
