Re: Implementing Incremental View Maintenance

Hi,

Attached is the latest patch (v14) to add support for Incremental Materialized
View Maintenance (IVM). It is possible to apply to current latest master branch.

Differences from the previous patch (v13) include:

* Support base tables using RLS

If a table has the Row Level Security (RLS) policy, IMMV is updated based on
the view owner's policy when a base table is updated. However, when a policy
of base table is changed or created after creating IMMV, IMMV is not updated
based on the new RLS policy. In this case, REFRESH command must be executed.

* Use ENR instead of temporary tables for internal operation

Previously, IVM create and use a temporary tables to store view delta rows.
However it caused out of shared memory, and Tom Lane pointed out that
using temp tables in IVM trigger is not good.

Currently, IVM uses tuplestores and ephemeral named relation (ENR) instead
of temporary tables. it doesn't cause previous problem like below:

testdb=# create table b1 (id integer, x numeric(10,3));
CREATE TABLE
testdb=# create incremental materialized view mv1
testdb-# as select id, count(*),sum(x) from b1 group by id;
SELECT 0
testdb=#
testdb=# do $$
testdb$# declare
testdb$# i integer;
testdb$# begin
testdb$# for i in 1..10000
testdb$# loop
testdb$# insert into b1 values (1,1);
testdb$# end loop;
testdb$# end;
testdb$# $$
testdb-# ;
DO
testdb=#

This issue is reported by PAscal.
https://www.postgresql.org/message-id/1577564109604-0.post@n3.nabble.com

* Support pg_dump/pg_restore for IVM

IVM supports pg_dump/pg_restore command.

* Prohibit rename and unique index creation on IVM columns

When a user make a unique index on ivm columns such as ivm_count, IVM will fail due to
the unique constraint violation, so IVM prohibits it.
Also, rename of these columns also causes IVM fails, so IVM prohibits it too.

* Fix incorrect WHERE condition check for outer-join views

The check for non null-rejecting condition check was incorrect.

Best Regards,
Takuma Hoshiai

--
Takuma Hoshiai <hoshiai(at)sraoss(dot)co(dot)jp>