Error occurs when updating user-defined type columns. Create an INCREMENTAL MATERIALIZED VIEW by specifying a query that includes user-defined type columns. After the view is created, an error occurs when inserting into the view source table (including the user-defined type column). ``` ERROR: operator does not exist ```
An execution example is shown below. ``` [ec2-user@ip-10-0-1-10 ivm]$ psql testdb -a -f extension-insert.sql -- -- pg_fraction: https://github.com/nuko-yokohama/pg_fraction -- DROP EXTENSION IF EXISTS pg_fraction CASCADE; psql:extension-insert.sql:4: NOTICE: drop cascades to column data of table foo DROP EXTENSION DROP TABLE IF EXISTS foo CASCADE; DROP TABLE CREATE EXTENSION IF NOT EXISTS pg_fraction; CREATE EXTENSION \dx List of installed extensions Name | Version | Schema | Description -------------+---------+------------+------------------------------ pg_fraction | 1.0 | public | fraction data type plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) \dT+ fraction List of data types Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description --------+----------+---------------+------+----------+----------+-------------------+------------- public | fraction | fraction | 16 | | postgres | | (1 row) CREATE TABLE foo (id int, data fraction); CREATE TABLE INSERT INTO foo (id, data) VALUES (1,'2/3'),(2,'1/3'),(3,'1/2'); INSERT 0 3 SELECT id, data FROM foo WHERE data >= '1/2'; id | data ----+------ 1 | 2/3 3 | 1/2 (2 rows) CREATE INCREMENTAL MATERIALIZED VIEW foo_imv AS SELECT id, data FROM foo WHERE data >= '1/2'; SELECT 2 TABLE foo_imv; id | data ----+------ 1 | 2/3 3 | 1/2 (2 rows) INSERT INTO foo (id, data) VALUES (4,'2/3'),(5,'2/5'),(6,'3/6'); -- error psql:extension-insert.sql:17: ERROR: operator does not exist: fraction pg_catalog.= fraction LINE 1: ...(mv.id IS NULL AND diff.id IS NULL)) AND (mv.data OPERATOR(p... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. QUERY: WITH updt AS (UPDATE public.foo_imv AS mv SET __ivm_count__ = mv.__ivm_count__ OPERATOR(pg_catalog.+) diff.__ivm_count__ FROM pg_temp_3.pg_temp_73900 AS diff WHERE (mv.id OPERATOR(pg_catalog.=) diff.id OR (mv.id IS NULL AND diff.id IS NULL)) AND (mv.data OPERATOR(pg_catalog.=) diff.data OR (mv.data IS NULL AND diff.data IS NULL)) RETURNING mv.id, mv.data) INSERT INTO public.foo_imv SELECT * FROM pg_temp_3.pg_temp_73900 AS diff WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE (mv.id OPERATOR(pg_catalog.=) diff.id OR (mv.id IS NULL AND diff.id IS NULL)) AND (mv.data OPERATOR(pg_catalog.=) diff.data OR (mv.data IS NULL AND diff.data IS NULL))); TABLE foo; id | data ----+------ 1 | 2/3 2 | 1/3 3 | 1/2 (3 rows) TABLE foo_imv; id | data ----+------ 1 | 2/3 3 | 1/2 (2 rows) DROP MATERIALIZED VIEW foo_imv; DROP MATERIALIZED VIEW INSERT INTO foo (id, data) VALUES (4,'2/3'),(5,'2/5'),(6,'3/6'); INSERT 0 3 TABLE foo; id | data ----+------ 1 | 2/3 2 | 1/3 3 | 1/2 4 | 2/3 5 | 2/5 6 | 1/2 (6 rows) ``` Best regards. 2018年12月27日(木) 21:57 Yugo Nagata <nag...@sraoss.co.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 <nag...@sraoss.co.jp> > >