[Bug] Matview Up-go-date maintenance feature is broken.

[reshke]

Cloudberry Database version

Matview up-go-date maintenance feature in CloudBerry is subsystem designed to answer, whether matview data is up-to-dat or not (refresh needed).

On current HEAD, it is broken. Multiple ways.

There are three ways to fool gp_matview_aux that I have discovered. Maybe there are more.

1. Table inheritance.
2. Rewrite rules
3. CTE

The latter two of them leads to incorrect query execution results

rewrite:

db2=# create table tt1(i int, j int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Cloudberry Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
db2=# create table tt2(i int, j int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Cloudberry Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
db2=# create materialized view mv1 as select * from ^C
db2=# insert into tt2 values(1,2),(2,2), (3,2),(1,4);
INSERT 0 4
db2=# create materialized view mv1 as select j from tt2 where i = 1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'j' as the Cloudberry Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT 2
db2=# create rule r1 as on insert TO tt1 do also insert into tt2 values(1,1);
CREATE RULE
db2=# analyze mv1 ;
ANALYZE
db2=# analyze tt2;
ANALYZE
db2=# select j from tt2 where i = 1;
 j
---
 2
 4
(2 rows)

db2=# table tt2;
 i | j
---+---
 2 | 2
 3 | 2
 1 | 2
 1 | 4
(4 rows)

db2=# insert into tt1 values(1,1);
INSERT 0 1
db2=# table tt2;
 i | j
---+---
 2 | 2
 3 | 2
 1 | 2
 1 | 4
 1 | 1
(5 rows)

db2=# select j from tt2 where i = 1;
 j
---
 2
 4
 1
(3 rows)
db2=# set enable_answer_query_using_materialized_views to true;
SET
db2=# select j from tt2 where i = 1;
 j
---
 2
 4
(2 rows)

db2=#

CTE:

db3=# set enable_answer_query_using_materialized_views to false;
SET
db3=# select count(1) from tt1 where i = 1;
 count
-------
    12
(1 row)

db3=# set enable_answer_query_using_materialized_views to true;
SET
db3=# select count(1) from tt1 where i = 1;
 count
-------
    12
(1 row)

db3=# with mod1 as (insert into tt1 values(1,1) returning *) select * from mod1;
 i | j
---+---
 1 | 1
(1 row)

db3=# select count(1) from tt1 where i = 1;
 count
-------
    12
(1 row)

db3=# set enable_answer_query_using_materialized_views to false;
SET
db3=# select count(1) from tt1 where i = 1;
 count
-------
    13
(1 row)

db3=#

I did not reproduce any incorrect result with table inheritance. This is bacause of has_subclass() check inside answer_query_using_materialized_views function.

The problem is here https://github.com/cloudberrydb/cloudberrydb/blob/main/src/backend/executor/execMain.c#L1047
We cannot rely on neither es_processed (rewrite rules case) nor (operation != CMD_SELECT) check (CTE case).

Rewrite rules case can be fixed via relhasrules check. This will disable AQUMV if relation once has rules. Including SELECT rules.

I dont have any fix for CTE for know. Maybe ill come up with something later.

Also, gp_matview_aux should be fixed. In says

db4=# create table tt1(i int, j int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Cloudberry Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
db4=# insert into tt1 select * from generate_series(1,10)a, generate_series(1,10)b;
INSERT 0 100
db4=# create table tt2 (i int) inherits(tt1);
NOTICE:  table has parent, setting distribution columns to match parent table
NOTICE:  merging column "i" with inherited definition
CREATE TABLE
db4=# insert into tt2 values(1,1);
INSERT 0 1
db4=# create materialized view m1 as select * from tt1 where i = 1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Cloudberry Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT 11
db4=# drop table tt2;
DROP TABLE
db4=# select ^C

db4=# table gp_matview_aux ;
 mvoid | mvname | datastatus
-------+--------+------------
 57503 | m1     | u
(1 rows)

It says datastatus = 'u' which is 100% false.

Also, why gp_matview_tables is shared catalog relation?

What happened

What you think should happen instead

How to reproduce

Operating System

Anything else

No response

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.

[avamingli]

1. Table inheritance.
2. Rewrite rules
3. CTE

Hi, thanks for report.
All of them are not supported for AQUMV now, gp_matview_tables is for AQUMV, access on it directly is pointless.
About 1, we could put this later as AQUMV has handled it(Also I'm working on partitioned tables which almost the same with inherit tables, the internal codes will be cherry-pick backed to CBDB)
2, 3 should be fixed.
I will take a look at this later.

[avamingli]

We cannot rely on neither es_processed (rewrite rules case)

For rules, on a fast glance, the es_processed return 0 rows for a rule-generated query, but the underlying relation data is changed.

Need to know if it's the same behavior of Postgres, or a bug from GPDB or CBDB.

[avamingli]

We cannot rely on neither es_processed (rewrite rules case)

For rules, on a fast glance, the es_processed return 0 rows for a rule-generated query, but the underlying relation data is changed.

Need to know if it's the same behavior of Postgres, or a bug from GPDB or CBDB.

Rules issue fixed in https://github.com/cloudberrydb/cloudberrydb/pull/584/files

[avamingli]

For inherit part issue, fixed in #587

[avamingli]

db3=# set enable_answer_query_using_materialized_views to false;
SET
db3=# select count(1) from tt1 where i = 1;
 count
-------
    12
(1 row)

db3=# set enable_answer_query_using_materialized_views to true;
SET
db3=# select count(1) from tt1 where i = 1;
 count
-------
    12
(1 row)

db3=# with mod1 as (insert into tt1 values(1,1) returning *) select * from mod1;
 i | j
---+---
 1 | 1
(1 row)

db3=# select count(1) from tt1 where i = 1;
 count
-------
    12
(1 row)

db3=# set enable_answer_query_using_materialized_views to false;
SET
db3=# select count(1) from tt1 where i = 1;
 count
-------
    13
(1 row)

db3=#

CTE cases can not reproduce, there must be some mat views not in the SQL, could you provide an independent case for me?
@reshke

[reshke]

CTE cases can not reproduce, there must be some mat views not in the SQL, could you provide an independent case for me? @reshke

Just reproduced this again on HEAD

reshke=# create database cleardb;
CREATE DATABASE
reshke=# \c cleardb
psql (14.4, server 14.4)
You are now connected to database "cleardb" as user "reshke".
cleardb=# create table (i int, j int) as select * from generate_series(1, 10)a,generate_series(1,10)b;
ERROR:  syntax error at or near "("
LINE 1: create table (i int, j int) as select * from generate_series...
                     ^
cleardb=# create table tt (i int, j int) as select * from generate_series(1, 10)a,generate_series(1,10)b;
ERROR:  syntax error at or near "as"
LINE 1: create table tt (i int, j int) as select * from generate_ser...
                                       ^
cleardb=# create table tt  as select * from generate_series(1, 10)a,generate_series(1,10)b;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'a' as the Cloudberry Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT 100
cleardb=# create materialized view mv1 as select b from tt where a = 1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'b' as the Cloudberry Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT 10
cleardb=# set enable_answer_query_using_materialized_views to true;
SET
cleardb=# analyze mv1, tt;
ANALYZE
cleardb=# explain analyze select b from tt where a = 1;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1.17 rows=10 width=4) (actual time=0.834..0.844 rows=10 loops=1)
   ->  Seq Scan on mv1  (cost=0.00..1.03 rows=3 width=4) (actual time=0.025..0.027 rows=5 loops=1)
 Planning Time: 76.336 ms
   (slice0)    Executor memory: 114K bytes.
   (slice1)    Executor memory: 114K bytes avg x 3x(0) workers, 114K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution Time: 1.468 ms
(8 rows)

cleardb=# with mod1 as (insert into tt values(1,1) returning *) select * from mod1;
 a | b
---+---
 1 | 1
(1 row)

cleardb=# explain analyze select b from tt where a = 1;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1.17 rows=10 width=4) (actual time=0.506..0.521 rows=10 loops=1)
   ->  Seq Scan on mv1  (cost=0.00..1.03 rows=3 width=4) (actual time=0.024..0.025 rows=5 loops=1)
 Planning Time: 60.415 ms
   (slice0)    Executor memory: 114K bytes.
   (slice1)    Executor memory: 114K bytes avg x 3x(0) workers, 114K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution Time: 1.116 ms
(8 rows)

cleardb=# select b from tt where a = 1;
 b
----
  5
  6
  9
 10
  2
  3
  4
  7
  8
  1
(10 rows)

cleardb=# set enable_answer_query_using_materialized_views to false;
SET
cleardb=# select b from tt where a = 1;
 b
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
  1
(11 rows)

cleardb=#

[avamingli]

All cases have been fixed, close this issue, thanks for report!