Re: Implementing Incremental View Maintenance

From: Takuma Hoshiai <hoshiai(at)sraoss(dot)co(dot)jp>
To: Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
Cc: pgsql-hackers(at)postgresql(dot)org, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>, michael(at)paquier(dot)xyz, amitlangote09(at)gmail(dot)com, alvherre(at)2ndquadrant(dot)com, thomas(dot)munro(at)gmail(dot)com, kgrittn(at)gmail(dot)com
Subject: Re: Implementing Incremental View Maintenance
Date: 2020-02-27 06:06:49
Message-ID: 20200227150649.101ef342d0e7d7abee320159@sraoss.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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>

Attachment Content-Type Size
IVM_patches_v14.tar.gz application/octet-stream 72.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2020-02-27 06:08:07 Re: Allow auto_explain to log plans before queries are executed
Previous Message Fujii Masao 2020-02-27 06:03:07 Re: [HACKERS] [WIP] Effective storage of duplicates in B-tree index.