Re: Implementing Incremental View Maintenance

"ROW LEVEL SECURITY" and INCREMENTAL MATERIALIZED VIEW.

Hi.

If ROW LEVEL SECURITY is set for the source table after creating the
INCREMENTAL MATELIALIZED VIEW, the search results by that are not reflected.
After setting ROW LEVEL SECURITY (similar to normal MATERIALIZED VIEW), you
need to execute REFRESH MATERILALIZED VIEW and reflect the result.
(Not limited to this, but in general cases where search results change by
means other than DML)

I propose to add this note to the document (rules.sgml).

execute log.

```
[ec2-user(at)ip-10-0-1-10 rls]$ psql testdb -e -f rls.sql
CREATE USER user_a;
CREATE ROLE
CREATE TABLE test (id int, data text);
CREATE TABLE
GRANT ALL ON TABLE test TO user_a;
GRANT
GRANT ALL ON SCHEMA public TO user_a;
GRANT
SET ROLE user_a;
SET
INSERT INTO test VALUES (1,'A'),(2,'B'),(3,'C');
INSERT 0 3
SELECT * FROM test;
id | data
----+------
1 | A
2 | B
3 | C
(3 rows)

CREATE VIEW test_v AS SELECT * FROM test;
CREATE VIEW
CREATE MATERIALIZED VIEW test_mv AS SELECT * FROM test;
SELECT 3
CREATE INCREMENTAL MATERIALIZED VIEW test_imv AS SELECT * FROM test;
SELECT 3
SELECT * FROM test_v;
id | data
----+------
1 | A
2 | B
3 | C
(3 rows)

SELECT * FROM test_mv;
id | data
----+------
1 | A
2 | B
3 | C
(3 rows)

SELECT * FROM test_imv;
id | data
----+------
3 | C
1 | A
2 | B
(3 rows)

RESET ROLE;
RESET
CREATE POLICY test_AAA ON test FOR SELECT TO user_a USING (data = 'A');
CREATE POLICY
ALTER TABLE test ENABLE ROW LEVEL SECURITY ;
ALTER TABLE
SET ROLE user_a;
SET
SELECT * FROM test_v;
id | data
----+------
1 | A
(1 row)

SELECT * FROM test_mv;
id | data
----+------
1 | A
2 | B
3 | C
(3 rows)

SELECT * FROM test_imv;
id | data
----+------
3 | C
1 | A
2 | B
(3 rows)

REFRESH MATERIALIZED VIEW test_mv;
REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW test_imv;
REFRESH MATERIALIZED VIEW
SELECT * FROM test_mv;
id | data
----+------
1 | A
(1 row)

SELECT * FROM test_imv;
id | data
----+------
1 | A
(1 row)

RESET ROLE;
RESET
REVOKE ALL ON TABLE test FROM user_a;
REVOKE
REVOKE ALL ON TABLE test_v FROM user_a;
REVOKE
REVOKE ALL ON TABLE test_mv FROM user_a;
REVOKE
REVOKE ALL ON TABLE test_imv FROM user_a;
REVOKE
REVOKE ALL ON SCHEMA public FROM user_a;
REVOKE
DROP TABLE test CASCADE;
psql:rls.sql:40: NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to view test_v
drop cascades to materialized view test_mv
drop cascades to materialized view test_imv
DROP TABLE
DROP USER user_a;
DROP ROLE
[ec2-user(at)ip-10-0-1-10 rls]$

```

Regard.

2018年12月27日(木) 21:57 Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>:

> Hi,
>
> I would like to implement Incremental View Maintenance (IVM) on
> PostgreSQL.
> IVM is a technique to maintain materialized views which computes and
> applies
> only the incremental changes to the materialized views rather than
> recomputate the contents as the current REFRESH command does.
>
> I had a presentation on our PoC implementation of IVM at PGConf.eu 2018
> [1].
> Our implementation uses row OIDs to compute deltas for materialized
> views.
> The basic idea is that if we have information about which rows in base
> tables
> are contributing to generate a certain row in a matview then we can
> identify
> the affected rows when a base table is updated. This is based on an idea of
> Dr. Masunaga [2] who is a member of our group and inspired from ID-based
> approach[3].
>
> In our implementation, the mapping of the row OIDs of the materialized view
> and the base tables are stored in "OID map". When a base relation is
> modified,
> AFTER trigger is executed and the delta is recorded in delta tables using
> the transition table feature. The accual udpate of the matview is triggerd
> by REFRESH command with INCREMENTALLY option.
>
> However, we realize problems of our implementation. First, WITH OIDS will
> be removed since PG12, so OIDs are no longer available. Besides this, it
> would
> be hard to implement this since it needs many changes of executor nodes to
> collect base tables's OIDs during execuing a query. Also, the cost of
> maintaining
> OID map would be high.
>
> For these reasons, we started to think to implement IVM without relying on
> OIDs
> and made a bit more surveys.
>
> We also looked at Kevin Grittner's discussion [4] on incremental matview
> maintenance. In this discussion, Kevin proposed to use counting algorithm
> [5]
> to handle projection views (using DISTNICT) properly. This algorithm need
> an
> additional system column, count_t, in materialized views and delta tables
> of
> base tables.
>
> However, the discussion about IVM is now stoped, so we would like to
> restart and
> progress this.
>
>
> Through our PoC inplementation and surveys, I think we need to think at
> least
> the followings for implementing IVM.
>
> 1. How to extract changes on base tables
>
> I think there would be at least two approaches for it.
>
> - Using transition table in AFTER triggers
> - Extracting changes from WAL using logical decoding
>
> In our PoC implementation, we used AFTER trigger and transition tables,
> but using
> logical decoding might be better from the point of performance of base
> table
> modification.
>
> If we can represent a change of UPDATE on a base table as query-like
> rather than
> OLD and NEW, it may be possible to update the materialized view directly
> instead
> of performing delete & insert.
>
>
> 2. How to compute the delta to be applied to materialized views
>
> Essentially, IVM is based on relational algebra. Theorically, changes on
> base
> tables are represented as deltas on this, like "R <- R + dR", and the
> delta on
> the materialized view is computed using base table deltas based on "change
> propagation equations". For implementation, we have to derive the
> equation from
> the view definition query (Query tree, or Plan tree?) and describe this as
> SQL
> query to compulte delta to be applied to the materialized view.
>
> There could be several operations for view definition: selection,
> projection,
> join, aggregation, union, difference, intersection, etc. If we can
> prepare a
> module for each operation, it makes IVM extensable, so we can start a
> simple
> view definition, and then support more complex views.
>
>
> 3. How to identify rows to be modifed in materialized views
>
> When applying the delta to the materialized view, we have to identify
> which row
> in the matview is corresponding to a row in the delta. A naive method is
> matching
> by using all columns in a tuple, but clearly this is unefficient. If
> thematerialized
> view has unique index, we can use this. Maybe, we have to force
> materialized views
> to have all primary key colums in their base tables. In our PoC
> implementation, we
> used OID to identify rows, but this will be no longer available as said
> above.
>
>
> 4. When to maintain materialized views
>
> There are two candidates of the timing of maintenance, immediate (eager)
> or deferred.
>
> In eager maintenance, the materialized view is updated in the same
> transaction
> where the base table is updated. In deferred maintenance, this is done
> after the
> transaction is commited, for example, when view is accessed, as a response
> to user
> request, etc.
>
> In the previous discussion[4], it is planned to start from "eager"
> approach. In our PoC
> implementaion, we used the other aproach, that is, using REFRESH command
> to perform IVM.
> I am not sure which is better as a start point, but I begin to think that
> the eager
> approach may be more simple since we don't have to maintain base table
> changes in other
> past transactions.
>
> In the eager maintenance approache, we have to consider a race condition
> where two
> different transactions change base tables simultaneously as discussed in
> [4].
>
>
> [1]
> https://www.postgresql.eu/events/pgconfeu2018/schedule/session/2195-implementing-incremental-view-maintenance-on-postgresql/
> [2]
> https://ipsj.ixsq.nii.ac.jp/ej/index.php?active_action=repository_view_main_item_detail&page_id=13&block_id=8&item_id=191254&item_no=1
> (Japanese only)
> [3] https://dl.acm.org/citation.cfm?id=2750546
> [4]
> https://www.postgresql.org/message-id/flat/1368561126.64093.YahooMailNeo%40web162904.mail.bf1.yahoo.com
> [5] https://dl.acm.org/citation.cfm?id=170066
>
> Regards,
> --
> Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>
>
>