Re: Implementing Incremental View Maintenance

Hello Takahashi-san,

On Tue, 3 Aug 2021 10:15:42 +0000
"r(dot)takahashi_2(at)fujitsu(dot)com" <r(dot)takahashi_2(at)fujitsu(dot)com> wrote:

> Hi Nagata-san,
>
>
> I am interested in this patch since it is good feature.
>
> I run some simple tests.
> I found the following problems.

Thank you for your interest for this patch!

> (1)
> Failed to "make world".
> I think there are extra "<lineitem>" in doc/src/sgml/ref/create_materialized_view.sgml
> (line 110 and 117)

Oops. I'll fix it.

> (2)
> In the case of partition, it seems that IVM does not work well.
> I run as follows.
>
> postgres=# create table parent (c int) partition by range (c);
> CREATE TABLE
> postgres=# create table child partition of parent for values from (1) to (100);
> CREATE TABLE
> postgres=# create incremental materialized view ivm_parent as select c from parent;
> NOTICE: could not create an index on materialized view "ivm_parent" automatically
> HINT: Create an index on the materialized view for efficient incremental maintenance.
> SELECT 0
> postgres=# create incremental materialized view ivm_child as select c from child;
> NOTICE: could not create an index on materialized view "ivm_child" automatically
> HINT: Create an index on the materialized view for efficient incremental maintenance.
> SELECT 0
> postgres=# insert into parent values (1);
> INSERT 0 1
> postgres=# insert into child values (2);
> INSERT 0 1
> postgres=# select * from parent;
> c
> ---
> 1
> 2
> (2 rows)
>
> postgres=# select * from child;
> c
> ---
> 1
> 2
> (2 rows)
>
> postgres=# select * from ivm_parent;
> c
> ---
> 1
> (1 row)
>
> postgres=# select * from ivm_child;
> c
> ---
> 2
> (1 row)
>
>
> I think ivm_parent and ivm_child should return 2 rows.

Good point!
I'll investigate this more, but we may have to prohibit views on partitioned
table and partitions.

> (3)
> I think IVM does not support foreign table, but try to make IVM.
>
> postgres=# create incremental materialized view ivm_foreign as select c from foreign_table;
> NOTICE: could not create an index on materialized view "ivm_foreign" automatically
> HINT: Create an index on the materialized view for efficient incremental maintenance.
> ERROR: "foreign_table" is a foreign table
> DETAIL: Triggers on foreign tables cannot have transition tables.
>
> It finally failed to make IVM, but I think it should be checked more early.

You are right. We don't support foreign tables as long as we use triggers.

I'll fix.

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>