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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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'.
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
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 0base/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
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 0base/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
Attachments:
matview-WAL-log-extension.patchtext/x-patch; name=matview-WAL-log-extension.patchDownload
*** 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);
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 0base/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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
Attachments:
typo.patchapplication/octet-stream; name=typo.patchDownload
diff --git a/doc/src/sgml/ref/alter_materialized_view.sgml b/doc/src/sgml/ref/alter_materialized_view.sgml
index b604513..e561706 100644
--- a/doc/src/sgml/ref/alter_materialized_view.sgml
+++ b/doc/src/sgml/ref/alter_materialized_view.sgml
@@ -15,7 +15,7 @@ PostgreSQL documentation
<refpurpose>change the definition of a materialized view</refpurpose>
</refnamediv>
- <indexterm zone="sql-alterview">
+ <indexterm zone="sql-altermaterializedview">
<primary>ALTER MATERIALIZED VIEW</primary>
</indexterm>
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
Attachments:
pg_dump_bugfix_matview.patchapplication/octet-stream; name=pg_dump_bugfix_matview.patchDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8404458..c6106dc 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -725,7 +725,8 @@ main(int argc, char **argv)
if (!schemaOnly)
{
getTableData(tblinfo, numTables, oids);
- buildMatViewRefreshDependencies(fout);
+ if (fout->remoteVersion >= 90300)
+ buildMatViewRefreshDependencies(fout);
if (dataOnly)
getTableDataFKConstraints();
}
@@ -1777,38 +1778,37 @@ buildMatViewRefreshDependencies(Archive *fout)
i_objid,
i_refobjid;
+ Assert(fout->remoteVersion >= 90300);
+
/* Make sure we are in proper schema */
selectSourceSchema(fout, "pg_catalog");
- if (fout->remoteVersion >= 90300)
- {
- appendPQExpBuffer(query, "with recursive w as "
- "( "
- "select d1.objid, d2.refobjid, c2.relkind as refrelkind "
- "from pg_depend d1 "
- "join pg_class c1 on c1.oid = d1.objid "
- "and c1.relkind = 'm' "
- "join pg_rewrite r1 on r1.ev_class = d1.objid "
- "join pg_depend d2 on d2.classid = 'pg_rewrite'::regclass "
- "and d2.objid = r1.oid "
- "and d2.refobjid <> d1.objid "
- "join pg_class c2 on c2.oid = d2.refobjid "
- "and c2.relkind in ('m','v') "
- "where d1.classid = 'pg_class'::regclass "
- "union "
- "select w.objid, d3.refobjid, c3.relkind "
- "from w "
- "join pg_rewrite r3 on r3.ev_class = w.refobjid "
- "join pg_depend d3 on d3.classid = 'pg_rewrite'::regclass "
- "and d3.objid = r3.oid "
- "and d3.refobjid <> w.refobjid "
- "join pg_class c3 on c3.oid = d3.refobjid "
- "and c3.relkind in ('m','v') "
- ") "
- "select 'pg_class'::regclass::oid as classid, objid, refobjid "
- "from w "
- "where refrelkind = 'm'");
- }
+ appendPQExpBuffer(query, "with recursive w as "
+ "( "
+ "select d1.objid, d2.refobjid, c2.relkind as refrelkind "
+ "from pg_depend d1 "
+ "join pg_class c1 on c1.oid = d1.objid "
+ "and c1.relkind = 'm' "
+ "join pg_rewrite r1 on r1.ev_class = d1.objid "
+ "join pg_depend d2 on d2.classid = 'pg_rewrite'::regclass "
+ "and d2.objid = r1.oid "
+ "and d2.refobjid <> d1.objid "
+ "join pg_class c2 on c2.oid = d2.refobjid "
+ "and c2.relkind in ('m','v') "
+ "where d1.classid = 'pg_class'::regclass "
+ "union "
+ "select w.objid, d3.refobjid, c3.relkind "
+ "from w "
+ "join pg_rewrite r3 on r3.ev_class = w.refobjid "
+ "join pg_depend d3 on d3.classid = 'pg_rewrite'::regclass "
+ "and d3.objid = r3.oid "
+ "and d3.refobjid <> w.refobjid "
+ "join pg_class c3 on c3.oid = d3.refobjid "
+ "and c3.relkind in ('m','v') "
+ ") "
+ "select 'pg_class'::regclass::oid as classid, objid, refobjid "
+ "from w "
+ "where refrelkind = 'm'");
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers