Re: Incremental View Maintenance, take 2
On Tue, 20 Aug 2024 at 02:14, Kirill Reshke wrote: > > > == Major suggestions. > > 1) At first glance, working with this IVM/IMMV infrastructure feels > really unintuitive about what servers actually do for query execution. > I do think It will be much better for user experience to add more > EXPLAIN about IVM work done inside IVM triggers. This way it is much > clearer which part is working slow, so which index should be created, > etc. > > 2) The kernel code for IVM lacks possibility to be extended for > further IVM optimizations. The one example is foreign key optimization > described here[1]. I'm not saying we should implement this within this > patchset, but we surely should pave the way for this. I don't have any > good suggestions for how to do this though. > > 3) I don't really think SQL design is good. CREATE [INCREMENTAL] M.V. > is too ad-hoc. I would prefer CREATE M.V. with (maintain_incr=true). > (reloption name is just an example). > This way we can change regular M.V. to IVM and vice versa via ALTER > M.V. SET *reloptions* - a type of syntax that is already present in > PostgreSQL core. > One little follow-up here. Why do we do prepstate visibility the way it is done? Can we instead export the snapshot in BEFORE trigger, save it somewhere and use it after? -- Best regards, Kirill Reshke
Re: Incremental View Maintenance, take 2
On Tue, 20 Aug 2024 at 02:14, Kirill Reshke wrote: > == Other thoughts > > In OLAP databases (see [2]), IVM opens the door for 'view > exploitation' feature. That is, use IVM (which is always up-to-date) > for query execution. But current IVM implementation is not compatible > with Cloudberry Append-optimized Table Access Method. The problem is > the 'table_tuple_fetch_row_version' call, which is used by > ivm_visible_in_prestate to check tuple visibility within a snapshot. I > am trying to solve this somehow. My current idea is the following: > multiple base table modification via single statement along with tuple > deletion from base tables are features. We can error-out these cases > (at M.V. creation time) all for some TAMs, and support only insert & > truncate. However, I don't know how to check if TAM supports > 'tuple_fetch_row_version' other than calling it and receiving > ERROR[3]. > I reread this and I find this a little bit unclear. What I'm proposing here is specifying the type of operations IVM supports on creation time. So, one can run CREATE IVM immv1 WITH (support_deletion = true/false, support_multiple_relation_change = true/false). Then, in the query execution time, we just ERROR if the query leads to deletion from IVM and support_deletion if false. -- Best regards, Kirill Reshke
Re: Incremental View Maintenance, take 2
On Tue, 30 Jul 2024 at 10:24, Yugo NAGATA wrote: > > Hi, > > On Tue, 30 Jul 2024 03:32:19 +0500 > Kirill Reshke wrote: > > > On Sat, 27 Jul 2024 at 13:26, Kirill Reshke wrote: > > > > > > Hi! > > > Cloudberry DB (Greenplum fork) uses IMMV feature for AQUMV (auto query > > > use matview) feature, so i got interested in how it is implemented. > > Thank you so much for a lot of comments! > I will respond to the comments soon. > > > > > > > On Thu, 11 Jul 2024 at 09:24, Yugo NAGATA wrote: > > > > > > > > I updated the patch to bump up the version numbers in psql and pg_dump > > > > codes > > > > from 17 to 18. > > > > > > Few suggestions: > > > > > > 1) `Add-relisivm-column-to-pg_class-system-catalog` commit message > > > should be fixed, there is "isimmv" in the last line. > > > 2) I dont get why `Add-Incremental-View-Maintenance-support.patch` > > > goes after 0005 & 0004. Shoulndt we first implement feature server > > > side, only when client (psql & pg_dump) side? > > > 3) Can we provide regression tests for each function separately? Test > > > for main feature in main patch, test for DISTINCT support in > > > v34-0007-Add-DISTINCT-support-for-IVM.patch etc? This way the patchset > > > will be easier to review, and can be committed separelety. > > > 4) v34-0006-Add-Incremental-View-Maintenance-support.patch no longer > > > applies due to 4b74ebf726d444ba820830cad986a1f92f724649. After > > > resolving issues manually, it does not compile, because > > > 4b74ebf726d444ba820830cad986a1f92f724649 also removes > > > save_userid/save_sec_context fields from ExecCreateTableAs. > > > > > > > if (RelationIsIVM(matviewRel) && stmt->skipData) > > > Now this function accepts skipData param. > > > > > > 5) For DISTINCT support patch uses hidden __ivm* columns. Is this > > > design discussed anywhere? I wonder if this is a necessity (only > > > solution) or if there are alternatives. > > > 6) > > > What are the caveats of supporting some simple cases for aggregation > > > funcs like in example? > > > ``` > > > regress=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_2 AS SELECT > > > sum(j) + sum(i) from mv_base_a; > > > ERROR: expression containing an aggregate in it is not supported on > > > incrementally maintainable materialized view > > > ``` > > > I can see some difficulties with division CREATE IMMV AS SELECT > > > 1/sum(i) from mv_base_a; (sum(i) == 0 case), but adding & > > > multiplication should be ok, aren't they? > > > > > > > > > Overall, patchset looks mature, however it is far from being > > > committable due to lack of testing/feedback/discussion. There is only > > > one way to fix this... Test and discuss it! > > > > > > > > > [1] https://github.com/cloudberrydb/cloudberrydb > > > > Hi! Small update: I tried to run a regression test and all > > IMMV-related tests failed on my vm. Maybe I'm doing something wrong, I > > will try to investigate. > > > > Another suggestion: support for \d and \d+ commands in psql. With v34 > > patchset applied, psql does not show anything IMMV-related in \d mode. > > > > ``` > > reshke=# \d m1 > >Materialized view "public.m1" > > Column | Type | Collation | Nullable | Default > > +-+---+--+- > > i | integer | | | > > Distributed by: (i) > > > > > > reshke=# \d+ m1 > > Materialized view "public.m1" > > Column | Type | Collation | Nullable | Default | Storage | > > Compression | Stats target | Description > > +-+---+--+-+-+-+--+- > > i | integer | | | | plain | > > | | > > View definition: > > SELECT t1.i > >FROM t1; > > Distributed by: (i) > > Access method: heap > > > > ``` > > > > Output should be 'Incrementally materialized view "public.m1"' IMO. > > > -- > Yugo NAGATA So, I spent another 2 weeks on this patch. I have read the whole 'Incremental View Maintenance' thread (from 2018), this thread, some related threads. Have studied some papers on this topic. I got a better understanding of the theory this work is backed up with. However, I still can add my 2c. == Major suggestions. 1) At first glance, working with this IVM/IMMV infrastructure feels really unintuitive about what servers actually do for query execution. I do think It will be much better for user experience to add more EXPLAIN about IVM work done inside IVM triggers. This way it is much clearer which part is working slow, so which index should be created, etc. 2) The kernel code for IVM lacks possibility to be extended for further IVM optimizations. The one example is foreign key optimization described here[1]. I'm not saying we should implement this within this patchset, but we surely should pave the way for this. I don't have any good suggestions for how to do this though. 3) I don't really think SQL design is good. CREATE [INCREME
Re: Incremental View Maintenance, take 2
On Wed, 31 May 2023 at 20:14, Yugo NAGATA wrote: > > Hello hackers, > > Here's a rebased version of the patch-set adding Incremental View > Maintenance support for PostgreSQL. That was discussed in [1]. > > The patch-set consists of the following eleven patches. > > - 0001: Add a syntax to create Incrementally Maintainable Materialized Views > - 0002: Add relisivm column to pg_class system catalog > - 0003: Allow to prolong life span of transition tables until transaction end > - 0004: Add Incremental View Maintenance support to pg_dum > - 0005: Add Incremental View Maintenance support to psql > - 0006: Add Incremental View Maintenance support > - 0007: Add DISTINCT support for IVM > - 0008: Add aggregates support in IVM > - 0009: Add support for min/max aggregates for IVM > - 0010: regression tests > - 0011: documentation > > [1] > https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp > > > Regards, > Yugo Nagata > > -- > Yugo NAGATA Actually, this new MV delta-table calculation can be used to make faster REFRESH MATERIALIZED VIEW even for non-IMMV. Specifically, we can use our cost-based Optimizer to decide which way is cheaper: regular query execution, or delta-table approach (if it is applicable). Is it worth another thread? -- Best regards, Kirill Reshke
Re: Incremental View Maintenance, take 2
I am really sorry for splitting my review comments into multiple emails. I'll try to do a better review in a future, all-in-one. On Thu, 11 Jul 2024 at 09:24, Yugo NAGATA wrote: > > On Tue, 2 Jul 2024 17:03:11 +0900 > Yugo NAGATA wrote: > > > On Sun, 31 Mar 2024 22:59:31 +0900 > > Yugo NAGATA wrote: > > > > > > > > Also, I added a comment on RelationIsIVM() macro persuggestion from > > > > jian he. > > > > In addition, I fixed a failure reported from cfbot on FreeBSD build > > > > caused by; > > > > > > > > WARNING: outfuncs/readfuncs failed to produce an equal rewritten > > > > parse tree > > > > > > > > This warning was raised since I missed to modify outfuncs.c for a new > > > > field. > > > > > > I found cfbot on FreeBSD still reported a failure due to > > > ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS because the regression test used > > > wrong role names. Attached is a fixed version, v32. > > > > Attached is a rebased version, v33. > > I updated the patch to bump up the version numbers in psql and pg_dump codes > from 17 to 18. > > Regards, > Yugo Nagata > > > > > Regards, > > Yugo Nagata > > > > > > -- > > Yugo NAGATA > > > -- > Yugo NAGATA 1) Provided patches do not set process title correctly: ``` reshke 2602433 18.7 0.1 203012 39760 ?Rs 20:41 1:58 postgres: reshke ivm [local] CREATE MATERIALIZED VIEW ``` 2) We allow to REFRESH IMMV. Why? IMMV should be always up to date. Well, I can see that this utility command may be useful in case of corruption of some base relation/view itself, so there will be a need to rebuild the whole from scratch. But we already have VACUUM FULL for this, aren't we? 3) Triggers created for IMMV are not listed via \dS [tablename] 4) apply_old_delta_with_count executes non-trivial SQL statements for IMMV. It would be really helpful to see this in EXPLAIN ANALYZE. 5) > + "DELETE FROM %s WHERE ctid IN (" > + "SELECT tid FROM (SELECT pg_catalog.row_number() over (partition by %s) AS > \"__ivm_row_number__\"," > + "mv.ctid AS tid," > + "diff.\"__ivm_count__\"" > + "FROM %s AS mv, %s AS diff " > + "WHERE %s) v " > + "WHERE v.\"__ivm_row_number__\" OPERATOR(pg_catalog.<=) > v.\"__ivm_count__\")", > + matviewname, > + keysbuf.data, > + matviewname, deltaname_old, > + match_cond); `SELECT pg_catalog.row_number()` is too generic to my taste. Maybe pg_catalog.immv_row_number() / pg_catalog.get_immv_row_number() ? 6) > +static void > +apply_new_delta(const char *matviewname, const char *deltaname_new, > + StringInfo target_list) > +{ > + StringInfoData querybuf; >+ > + /* Search for matching tuples from the view and update or delete if found. > */ Is this comment correct? we only insert tuples here? 7) During patch development, one should pick OIDs from range 8000- > +# IVM > +{ oid => '786', descr => 'ivm trigger (before)', > + proname => 'IVM_immediate_before', provolatile => 'v', prorettype => > 'trigger', > + proargtypes => '', prosrc => 'IVM_immediate_before' }, > +{ oid => '787', descr => 'ivm trigger (after)', > + proname => 'IVM_immediate_maintenance', provolatile => 'v', prorettype => > 'trigger', > + proargtypes => '', prosrc => 'IVM_immediate_maintenance' }, > +{ oid => '788', descr => 'ivm filetring ', > + proname => 'ivm_visible_in_prestate', provolatile => 's', prorettype => > 'bool', > + proargtypes => 'oid tid oid', prosrc => 'ivm_visible_in_prestate' }, > ] -- Best regards, Kirill Reshke
Re: Incremental View Maintenance, take 2
On Thu, 11 Jul 2024 at 09:24, Yugo NAGATA wrote: > > On Tue, 2 Jul 2024 17:03:11 +0900 > Yugo NAGATA wrote: > > > On Sun, 31 Mar 2024 22:59:31 +0900 > > Yugo NAGATA wrote: > > > > > > > > Also, I added a comment on RelationIsIVM() macro persuggestion from > > > > jian he. > > > > In addition, I fixed a failure reported from cfbot on FreeBSD build > > > > caused by; > > > > > > > > WARNING: outfuncs/readfuncs failed to produce an equal rewritten > > > > parse tree > > > > > > > > This warning was raised since I missed to modify outfuncs.c for a new > > > > field. > > > > > > I found cfbot on FreeBSD still reported a failure due to > > > ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS because the regression test used > > > wrong role names. Attached is a fixed version, v32. > > > > Attached is a rebased version, v33. > > I updated the patch to bump up the version numbers in psql and pg_dump codes > from 17 to 18. > > Regards, > Yugo Nagata > > > > > Regards, > > Yugo Nagata > > > > > > -- > > Yugo NAGATA > > > -- > Yugo NAGATA Small updates with something o found recent days: ``` db2=# create incremental materialized view v2 as select * from v1; ERROR: VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view ``` Error messaging is not true, create view v2 as select * from v1; works fine. ``` db2=# create incremental materialized view vv2 as select i,j2, i / j2 from t1 join t2 on true; db2=# insert into t2 values(1,0); ERROR: division by zero ``` It is very strange to receive `division by zero` while inserting into relation, isn't it? Can we add some hints/CONTEXT here? Regular triggers do it: ``` db2=# insert into ttt values(10,0); ERROR: division by zero CONTEXT: PL/pgSQL function f1() line 3 at IF ``` -- Best regards, Kirill Reshke
Re: Incremental View Maintenance, take 2
On Tue, 30 Jul 2024 at 03:32, Kirill Reshke wrote: > > On Sat, 27 Jul 2024 at 13:26, Kirill Reshke wrote: > > > > Hi! > > Cloudberry DB (Greenplum fork) uses IMMV feature for AQUMV (auto query > > use matview) feature, so i got interested in how it is implemented. > > > > On Thu, 11 Jul 2024 at 09:24, Yugo NAGATA wrote: > > > > > > I updated the patch to bump up the version numbers in psql and pg_dump > > > codes > > > from 17 to 18. > > > > Few suggestions: > > > > 1) `Add-relisivm-column-to-pg_class-system-catalog` commit message > > should be fixed, there is "isimmv" in the last line. > > 2) I dont get why `Add-Incremental-View-Maintenance-support.patch` > > goes after 0005 & 0004. Shoulndt we first implement feature server > > side, only when client (psql & pg_dump) side? > > 3) Can we provide regression tests for each function separately? Test > > for main feature in main patch, test for DISTINCT support in > > v34-0007-Add-DISTINCT-support-for-IVM.patch etc? This way the patchset > > will be easier to review, and can be committed separelety. > > 4) v34-0006-Add-Incremental-View-Maintenance-support.patch no longer > > applies due to 4b74ebf726d444ba820830cad986a1f92f724649. After > > resolving issues manually, it does not compile, because > > 4b74ebf726d444ba820830cad986a1f92f724649 also removes > > save_userid/save_sec_context fields from ExecCreateTableAs. > > > > > if (RelationIsIVM(matviewRel) && stmt->skipData) > > Now this function accepts skipData param. > > > > 5) For DISTINCT support patch uses hidden __ivm* columns. Is this > > design discussed anywhere? I wonder if this is a necessity (only > > solution) or if there are alternatives. > > 6) > > What are the caveats of supporting some simple cases for aggregation > > funcs like in example? > > ``` > > regress=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_2 AS SELECT > > sum(j) + sum(i) from mv_base_a; > > ERROR: expression containing an aggregate in it is not supported on > > incrementally maintainable materialized view > > ``` > > I can see some difficulties with division CREATE IMMV AS SELECT > > 1/sum(i) from mv_base_a; (sum(i) == 0 case), but adding & > > multiplication should be ok, aren't they? > > > > > > Overall, patchset looks mature, however it is far from being > > committable due to lack of testing/feedback/discussion. There is only > > one way to fix this... Test and discuss it! > > > > > > [1] https://github.com/cloudberrydb/cloudberrydb > > Hi! Small update: I tried to run a regression test and all > IMMV-related tests failed on my vm. Maybe I'm doing something wrong, I > will try to investigate. > > Another suggestion: support for \d and \d+ commands in psql. With v34 > patchset applied, psql does not show anything IMMV-related in \d mode. > > ``` > reshke=# \d m1 >Materialized view "public.m1" > Column | Type | Collation | Nullable | Default > +-+---+--+- > i | integer | | | > Distributed by: (i) > > > reshke=# \d+ m1 > Materialized view "public.m1" > Column | Type | Collation | Nullable | Default | Storage | > Compression | Stats target | Description > +-+---+--+-+-+-+--+- > i | integer | | | | plain | > | | > View definition: > SELECT t1.i >FROM t1; > Distributed by: (i) > Access method: heap > > ``` > > Output should be 'Incrementally materialized view "public.m1"' IMO. And one more thing, noticed today while playing with patchset: I believe non-terminal incremental should be OptIncremental Im talking about this: ``` incremental: INCREMENTAL { $$ = true; } | /*EMPTY*/ { $$ = false; } ; ```
Re: Incremental View Maintenance, take 2
Hi, On Tue, 30 Jul 2024 03:32:19 +0500 Kirill Reshke wrote: > On Sat, 27 Jul 2024 at 13:26, Kirill Reshke wrote: > > > > Hi! > > Cloudberry DB (Greenplum fork) uses IMMV feature for AQUMV (auto query > > use matview) feature, so i got interested in how it is implemented. Thank you so much for a lot of comments! I will respond to the comments soon. > > > > On Thu, 11 Jul 2024 at 09:24, Yugo NAGATA wrote: > > > > > > I updated the patch to bump up the version numbers in psql and pg_dump > > > codes > > > from 17 to 18. > > > > Few suggestions: > > > > 1) `Add-relisivm-column-to-pg_class-system-catalog` commit message > > should be fixed, there is "isimmv" in the last line. > > 2) I dont get why `Add-Incremental-View-Maintenance-support.patch` > > goes after 0005 & 0004. Shoulndt we first implement feature server > > side, only when client (psql & pg_dump) side? > > 3) Can we provide regression tests for each function separately? Test > > for main feature in main patch, test for DISTINCT support in > > v34-0007-Add-DISTINCT-support-for-IVM.patch etc? This way the patchset > > will be easier to review, and can be committed separelety. > > 4) v34-0006-Add-Incremental-View-Maintenance-support.patch no longer > > applies due to 4b74ebf726d444ba820830cad986a1f92f724649. After > > resolving issues manually, it does not compile, because > > 4b74ebf726d444ba820830cad986a1f92f724649 also removes > > save_userid/save_sec_context fields from ExecCreateTableAs. > > > > > if (RelationIsIVM(matviewRel) && stmt->skipData) > > Now this function accepts skipData param. > > > > 5) For DISTINCT support patch uses hidden __ivm* columns. Is this > > design discussed anywhere? I wonder if this is a necessity (only > > solution) or if there are alternatives. > > 6) > > What are the caveats of supporting some simple cases for aggregation > > funcs like in example? > > ``` > > regress=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_2 AS SELECT > > sum(j) + sum(i) from mv_base_a; > > ERROR: expression containing an aggregate in it is not supported on > > incrementally maintainable materialized view > > ``` > > I can see some difficulties with division CREATE IMMV AS SELECT > > 1/sum(i) from mv_base_a; (sum(i) == 0 case), but adding & > > multiplication should be ok, aren't they? > > > > > > Overall, patchset looks mature, however it is far from being > > committable due to lack of testing/feedback/discussion. There is only > > one way to fix this... Test and discuss it! > > > > > > [1] https://github.com/cloudberrydb/cloudberrydb > > Hi! Small update: I tried to run a regression test and all > IMMV-related tests failed on my vm. Maybe I'm doing something wrong, I > will try to investigate. > > Another suggestion: support for \d and \d+ commands in psql. With v34 > patchset applied, psql does not show anything IMMV-related in \d mode. > > ``` > reshke=# \d m1 >Materialized view "public.m1" > Column | Type | Collation | Nullable | Default > +-+---+--+- > i | integer | | | > Distributed by: (i) > > > reshke=# \d+ m1 > Materialized view "public.m1" > Column | Type | Collation | Nullable | Default | Storage | > Compression | Stats target | Description > +-+---+--+-+-+-+--+- > i | integer | | | | plain | > | | > View definition: > SELECT t1.i >FROM t1; > Distributed by: (i) > Access method: heap > > ``` > > Output should be 'Incrementally materialized view "public.m1"' IMO. -- Yugo NAGATA
Re: Incremental View Maintenance, take 2
On Sat, 27 Jul 2024 at 13:26, Kirill Reshke wrote: > > Hi! > Cloudberry DB (Greenplum fork) uses IMMV feature for AQUMV (auto query > use matview) feature, so i got interested in how it is implemented. > > On Thu, 11 Jul 2024 at 09:24, Yugo NAGATA wrote: > > > > I updated the patch to bump up the version numbers in psql and pg_dump codes > > from 17 to 18. > > Few suggestions: > > 1) `Add-relisivm-column-to-pg_class-system-catalog` commit message > should be fixed, there is "isimmv" in the last line. > 2) I dont get why `Add-Incremental-View-Maintenance-support.patch` > goes after 0005 & 0004. Shoulndt we first implement feature server > side, only when client (psql & pg_dump) side? > 3) Can we provide regression tests for each function separately? Test > for main feature in main patch, test for DISTINCT support in > v34-0007-Add-DISTINCT-support-for-IVM.patch etc? This way the patchset > will be easier to review, and can be committed separelety. > 4) v34-0006-Add-Incremental-View-Maintenance-support.patch no longer > applies due to 4b74ebf726d444ba820830cad986a1f92f724649. After > resolving issues manually, it does not compile, because > 4b74ebf726d444ba820830cad986a1f92f724649 also removes > save_userid/save_sec_context fields from ExecCreateTableAs. > > > if (RelationIsIVM(matviewRel) && stmt->skipData) > Now this function accepts skipData param. > > 5) For DISTINCT support patch uses hidden __ivm* columns. Is this > design discussed anywhere? I wonder if this is a necessity (only > solution) or if there are alternatives. > 6) > What are the caveats of supporting some simple cases for aggregation > funcs like in example? > ``` > regress=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_2 AS SELECT > sum(j) + sum(i) from mv_base_a; > ERROR: expression containing an aggregate in it is not supported on > incrementally maintainable materialized view > ``` > I can see some difficulties with division CREATE IMMV AS SELECT > 1/sum(i) from mv_base_a; (sum(i) == 0 case), but adding & > multiplication should be ok, aren't they? > > > Overall, patchset looks mature, however it is far from being > committable due to lack of testing/feedback/discussion. There is only > one way to fix this... Test and discuss it! > > > [1] https://github.com/cloudberrydb/cloudberrydb Hi! Small update: I tried to run a regression test and all IMMV-related tests failed on my vm. Maybe I'm doing something wrong, I will try to investigate. Another suggestion: support for \d and \d+ commands in psql. With v34 patchset applied, psql does not show anything IMMV-related in \d mode. ``` reshke=# \d m1 Materialized view "public.m1" Column | Type | Collation | Nullable | Default +-+---+--+- i | integer | | | Distributed by: (i) reshke=# \d+ m1 Materialized view "public.m1" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +-+---+--+-+-+-+--+- i | integer | | | | plain | | | View definition: SELECT t1.i FROM t1; Distributed by: (i) Access method: heap ``` Output should be 'Incrementally materialized view "public.m1"' IMO.
Re: Incremental View Maintenance, take 2
Hi! Cloudberry DB (Greenplum fork) uses IMMV feature for AQUMV (auto query use matview) feature, so i got interested in how it is implemented. On Thu, 11 Jul 2024 at 09:24, Yugo NAGATA wrote: > > I updated the patch to bump up the version numbers in psql and pg_dump codes > from 17 to 18. Few suggestions: 1) `Add-relisivm-column-to-pg_class-system-catalog` commit message should be fixed, there is "isimmv" in the last line. 2) I dont get why `Add-Incremental-View-Maintenance-support.patch` goes after 0005 & 0004. Shoulndt we first implement feature server side, only when client (psql & pg_dump) side? 3) Can we provide regression tests for each function separately? Test for main feature in main patch, test for DISTINCT support in v34-0007-Add-DISTINCT-support-for-IVM.patch etc? This way the patchset will be easier to review, and can be committed separelety. 4) v34-0006-Add-Incremental-View-Maintenance-support.patch no longer applies due to 4b74ebf726d444ba820830cad986a1f92f724649. After resolving issues manually, it does not compile, because 4b74ebf726d444ba820830cad986a1f92f724649 also removes save_userid/save_sec_context fields from ExecCreateTableAs. > if (RelationIsIVM(matviewRel) && stmt->skipData) Now this function accepts skipData param. 5) For DISTINCT support patch uses hidden __ivm* columns. Is this design discussed anywhere? I wonder if this is a necessity (only solution) or if there are alternatives. 6) What are the caveats of supporting some simple cases for aggregation funcs like in example? ``` regress=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_2 AS SELECT sum(j) + sum(i) from mv_base_a; ERROR: expression containing an aggregate in it is not supported on incrementally maintainable materialized view ``` I can see some difficulties with division CREATE IMMV AS SELECT 1/sum(i) from mv_base_a; (sum(i) == 0 case), but adding & multiplication should be ok, aren't they? Overall, patchset looks mature, however it is far from being committable due to lack of testing/feedback/discussion. There is only one way to fix this... Test and discuss it! [1] https://github.com/cloudberrydb/cloudberrydb
Re: Incremental View Maintenance, take 2
On Mon, 4 Sep 2023 16:48:02 +0800 jian he wrote: > other ideas based on v29. > > src/include/utils/rel.h > 680: #define RelationIsIVM(relation) ((relation)->rd_rel->relisivm) > I guess it would be better to add some comments to address the usage. > Since all peer macros all have some comments. OK. I will add comments on this macro. > pg_class change, I guess we need bump CATALOG_VERSION_NO? CATALOG_VERSION_NO is frequently bumped up when new features are committed, so including it in the patch causes frequent needs for rebase during the review of the patch even if no meaningful change is made. Therefore, I wonder we don't have to included it in the patch at this time. > small issue. makeIvmAggColumn and calc_delta need to add an empty > return statement? I'm sorry but I could not understand what you suggested, so could you give me more explanation? > style issue. in gram.y, "incremental" upper case? > + CREATE OptNoLog incremental MATERIALIZED VIEW > create_mv_target AS SelectStmt opt_with_data This "incremental" is defined as INCREMENTAL or empty, as below. incremental:INCREMENTAL { $$ = true; } | /*EMPTY*/ { $$ = false; } > I don't know how pgident works, do you need to add some keywords to > src/tools/pgindent/typedefs.list to make indentation work? I'm not sure typedefs.list should be updated in each patch, because tools/pgindent/README said that the latest typedef file is downloaded from the buildfarm when pgindent is run. > in > /* If this is not the last AFTER trigger call, immediately exit. */ > Assert (entry->before_trig_count >= entry->after_trig_count); > if (entry->before_trig_count != entry->after_trig_count) > return PointerGetDatum(NULL); > > before returning NULL, do you also need clean_up_IVM_hash_entry? (I > don't know when this case will happen) No, clean_up_IVM_hash_entry is not necessary in this case. When multiple tables are updated in a statement, statement-level AFTER triggers collects every information of the tables, and the last AFTER trigger have to perform the actual maintenance of the view. To make sure this, the number that BEFORE and AFTER trigger is fired is counted respectively, and when they match it is regarded the last AFTER trigger call performing the maintenance. Until this, collected information have to keep, so we cannot call clean_up_IVM_hash_entry. > in > /* Replace the modified table with the new delta table and > calculate the new view delta*/ > replace_rte_with_delta(rte, table, true, queryEnv); > refresh_matview_datafill(dest_new, query, queryEnv, tupdesc_new, ""); > > replace_rte_with_delta does not change the argument: table, argument: > queryEnv. refresh_matview_datafill just uses the partial argument of > the function calc_delta. So I guess, I am confused by the usage of > replace_rte_with_delta. also I think it should return void, since you > just modify the input argument. Here refresh_matview_datafill is just > persisting new delta content to dest_new? Yes, refresh_matview_datafill executes the query and the result rows to "dest_new". And, replace_rte_with_delta updates the input argument "rte" and returns the result to it, so it may be better that this returns void, as you suggested. Regards, Yugo Nagata -- Yugo NAGATA
Re: Incremental View Maintenance, take 2
On Fri, 1 Sep 2023 15:42:17 +0800 jian he wrote: I apologize for this late reply. > I added a new function append_update_set_caluse, and deleted > functions: {append_set_clause_for_count, append_set_clause_for_sum, > append_set_clause_for_avg, append_set_clause_for_minmax} > > I guess this way is more extensible/generic than yours. Do you mean that consolidating such functions to a general function make easier to support a new aggregate function in future? I'm not convinced completely yet it because your suggestion seems that every functions' logic are just put into a new function, but providing a common interface might make a sense a bit. By the way, when you attach files other than updated patches that can be applied to master branch, using ".patch" or ".diff" as the file extension help to avoid to confuse cfbot (for example, like basedon_v29_matview_c_refactor_update_set_clause.patch.txt). > src/backend/commands/matview.c > 2268: /* For tuple deletion */ > maybe "/* For tuple deletion and update*/" is more accurate? This "deletion" means deletion of tuple from the view rather than DELETE statement, so I think this is ok. > Since the apply delta query is quite complex, I feel like adding some > "if debug then print out the final querybuf.data end if" would be a > good idea. Agreed, it would be helpful for debugging. I think it would be good to add a debug macro that works if DEBUG_IVM is defined rather than adding GUC like debug_print_..., how about it? > we add hidden columns somewhere, also to avoid corner cases, so maybe > somewhere we should assert total attribute number is sane. The number of hidden columns to be added depends on the view definition query, so I wonder the Assert condition would be a bit complex. Could you explain what are you assume about like for example? Regards, Yugo Nagata -- Yugo NAGATA
Re: Incremental View Maintenance, take 2
On Mon, 22 Jan 2024 13:51:08 +1100 Peter Smith wrote: > 2024-01 Commitfest. > > Hi, This patch has a CF status of "Needs Review" [1], but it seems > like there was some CFbot test failure last time it was run [2]. > Please have a look and post an updated version if necessary. Thank you for pointing out it. The CFbot failure is caused by a post [1] not by my patch-set, but regardless of it, I will heck if we need rebase and send the new version if necessary soon. [1] https://www.postgresql.org/message-id/CACJufxEoCCJE1vntJp1SWjen8vBUa3vZLgL%3DswPwar4zim976g%40mail.gmail.com Regards, Yugo Nagata > == > [1] https://commitfest.postgresql.org/46/4337/ > [2] https://cirrus-ci.com/task/6607979311529984 > > Kind Regards, > Peter Smith. -- Yugo NAGATA
Re: Incremental View Maintenance, take 2
2024-01 Commitfest. Hi, This patch has a CF status of "Needs Review" [1], but it seems like there was some CFbot test failure last time it was run [2]. Please have a look and post an updated version if necessary. == [1] https://commitfest.postgresql.org/46/4337/ [2] https://cirrus-ci.com/task/6607979311529984 Kind Regards, Peter Smith.
Re: Incremental View Maintenance, take 2
On Sat, Sep 2, 2023 at 7:46 PM Tatsuo Ishii wrote: > > > attached is my refactor. there is some whitespace errors in the > > patches, you need use > > git apply --reject --whitespace=fix > > basedon_v29_matview_c_refactor_update_set_clause.patch > > > > Also you patch cannot use git apply, i finally found out bulk apply > > I have no problem with applying Yugo's v29 patches using git apply, no > white space errors. > thanks. I downloaded the patches from the postgres website, then the problem was solved. other ideas based on v29. src/include/utils/rel.h 680: #define RelationIsIVM(relation) ((relation)->rd_rel->relisivm) I guess it would be better to add some comments to address the usage. Since all peer macros all have some comments. pg_class change, I guess we need bump CATALOG_VERSION_NO? small issue. makeIvmAggColumn and calc_delta need to add an empty return statement? style issue. in gram.y, "incremental" upper case? + CREATE OptNoLog incremental MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data I don't know how pgident works, do you need to add some keywords to src/tools/pgindent/typedefs.list to make indentation work? in /* If this is not the last AFTER trigger call, immediately exit. */ Assert (entry->before_trig_count >= entry->after_trig_count); if (entry->before_trig_count != entry->after_trig_count) return PointerGetDatum(NULL); before returning NULL, do you also need clean_up_IVM_hash_entry? (I don't know when this case will happen) in /* Replace the modified table with the new delta table and calculate the new view delta*/ replace_rte_with_delta(rte, table, true, queryEnv); refresh_matview_datafill(dest_new, query, queryEnv, tupdesc_new, ""); replace_rte_with_delta does not change the argument: table, argument: queryEnv. refresh_matview_datafill just uses the partial argument of the function calc_delta. So I guess, I am confused by the usage of replace_rte_with_delta. also I think it should return void, since you just modify the input argument. Here refresh_matview_datafill is just persisting new delta content to dest_new?
Re: Incremental View Maintenance, take 2
> attached is my refactor. there is some whitespace errors in the > patches, you need use > git apply --reject --whitespace=fix > basedon_v29_matview_c_refactor_update_set_clause.patch > > Also you patch cannot use git apply, i finally found out bulk apply I have no problem with applying Yugo's v29 patches using git apply, no white space errors. $ git apply ~/v29* (the patches are saved under my home directory). I suggest you to check your email application whether it correctly saved the patch files for you. FYI, here are results from sha256sum: ffac37cb455788c1105ffc01c6b606de75f53321c2f235f7efa19f3f52d12b9e v29-0001-Add-a-syntax-to-create-Incrementally-Maintainabl.patch f684485e7c9ac1b2990943a3c73fa49a9091a268917547d9e116baef5118cca7 v29-0002-Add-relisivm-column-to-pg_class-system-catalog.patch fcf5bc8ae562ed1c2ab397b499544ddab03ad2c3acb2263d0195a3ec799b131c v29-0003-Allow-to-prolong-life-span-of-transition-tables-.patch a7a13ef8e73c4717166db079d5607f78d21199379de341a0e8175beef5ea1c1a v29-0004-Add-Incremental-View-Maintenance-support-to-pg_d.patch a2aa51d035774867bfab1580ef14143998dc71c1b941bd1a3721dc019bc62649 v29-0005-Add-Incremental-View-Maintenance-support-to-psql.patch fe0225d761a08eb80082f1a2c039b9b8b20626169b03abaf649db9c74fe99194 v29-0006-Add-Incremental-View-Maintenance-support.patch 68b007befedcf92fc83ab8c3347ac047a50816f061c77b69281e12d52944db82 v29-0007-Add-DISTINCT-support-for-IVM.patch 2201241a22095f736a17383fc8b26d48a459ebf1c2f5cf120896cfc0ce5e03e4 v29-0008-Add-aggregates-support-in-IVM.patch 6390117c559bf1585349c5a09b77b784e086ccc22eb530cd364ce78371c66741 v29-0009-Add-support-for-min-max-aggregates-for-IVM.patch 7019a116c64127783bd9c682ddf1ee3792286d0e41c91a33010111e7be2c9459 v29-0010-Add-regression-tests-for-Incremental-View-Mainte.patch 189afdc7da866bd958e2d554ba12adf93d7e6d0acb581290a48d72fcf640e243 v29-0011-Add-documentations-about-Incremental-View-Mainte.patch Best reagards, -- Tatsuo Ishii SRA OSS LLC English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp
Re: Incremental View Maintenance, take 2
hi based on v29. based on https://stackoverflow.com/a/4014981/1560347: I added a new function append_update_set_caluse, and deleted functions: {append_set_clause_for_count, append_set_clause_for_sum, append_set_clause_for_avg, append_set_clause_for_minmax} I guess this way is more extensible/generic than yours. replaced the following code with the generic function: append_update_set_caluse. + /* For views with aggregates, we need to build SET clause for updating aggregate + * values. */ + if (query->hasAggs && IsA(tle->expr, Aggref)) + { + Aggref *aggref = (Aggref *) tle->expr; + const char *aggname = get_func_name(aggref->aggfnoid); + + /* + * We can use function names here because it is already checked if these + * can be used in IMMV by its OID at the definition time. + */ + + /* count */ + if (!strcmp(aggname, "count")) + append_set_clause_for_count(resname, aggs_set_old, aggs_set_new, aggs_list_buf); + + /* sum */ + else if (!strcmp(aggname, "sum")) + append_set_clause_for_sum(resname, aggs_set_old, aggs_set_new, aggs_list_buf); + + /* avg */ + else if (!strcmp(aggname, "avg")) + append_set_clause_for_avg(resname, aggs_set_old, aggs_set_new, aggs_list_buf, + format_type_be(aggref->aggtype)); + + else + elog(ERROR, "unsupported aggregate function: %s", aggname); + } --<<< attached is my refactor. there is some whitespace errors in the patches, you need use git apply --reject --whitespace=fix basedon_v29_matview_c_refactor_update_set_clause.patch Also you patch cannot use git apply, i finally found out bulk apply using gnu patch from https://serverfault.com/questions/102324/apply-multiple-patch-files. previously I just did it manually one by one. I think if you use { for i in $PATCHES/v29*.patch; do patch -p1 < $i; done } GNU patch, it will generate an .orig file for every modified file? -< src/backend/commands/matview.c 2268: /* For tuple deletion */ maybe "/* For tuple deletion and update*/" is more accurate? -< currently at here: src/test/regress/sql/incremental_matview.sql 98: -- support SUM(), COUNT() and AVG() aggregate functions 99: BEGIN; 100: CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i; 101: SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4; 102: INSERT INTO mv_base_a VALUES(2,100); src/backend/commands/matview.c 2858: if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT) 2859: elog(ERROR, "SPI_exec failed: %s", querybuf.data); then I debug, print out querybuf.data: WITH updt AS (UPDATE public.mv_ivm_agg AS mv SET __ivm_count__ = mv.__ivm_count__ OPERATOR(pg_catalog.+) diff.__ivm_count__ , sum = (CASE WHEN mv.__ivm_count_sum__ OPERATOR(pg_catalog.=) 0 AND diff.__ivm_count_sum__ OPERATOR(pg_catalog.=) 0 THEN NULL WHEN mv.sum IS NULL THEN diff.sum WHEN diff.sum IS NULL THEN mv.sum ELSE (mv.sum OPERATOR(pg_catalog.+) diff.sum) END), __ivm_count_sum__ = (mv.__ivm_count_sum__ OPERATOR(pg_catalog.+) diff.__ivm_count_sum__), count = (mv.count OPERATOR(pg_catalog.+) diff.count), avg = (CASE WHEN mv.__ivm_count_avg__ OPERATOR(pg_catalog.=) 0 AND diff.__ivm_count_avg__ OPERATOR(pg_catalog.=) 0 THEN NULL WHEN mv.__ivm_sum_avg__ IS NULL THEN diff.__ivm_sum_avg__ WHEN diff.__ivm_sum_avg__ IS NULL THEN mv.__ivm_sum_avg__ ELSE (mv.__ivm_sum_avg__ OPERATOR(pg_catalog.+) diff.__ivm_sum_avg__)::numeric END) OPERATOR(pg_catalog./) (mv.__ivm_count_avg__ OPERATOR(pg_catalog.+) diff.__ivm_count_avg__), __ivm_sum_avg__ = (CASE WHEN mv.__ivm_count_avg__ OPERATOR(pg_catalog.=) 0 AND diff.__ivm_count_avg__ OPERATOR(pg_catalog.=) 0 THEN NULL WHEN mv.__ivm_sum_avg__ IS NULL THEN diff.__ivm_sum_avg__ WHEN diff.__ivm_sum_avg__ IS NULL THEN mv.__ivm_sum_avg__ ELSE (mv.__ivm_sum_avg__ OPERATOR(pg_catalog.+) diff.__ivm_sum_avg__) END), __ivm_count_avg__ = (mv.__ivm_count_avg__ OPERATOR(pg_catalog.+) diff.__ivm_count_avg__) FROM new_delta AS diff WHERE (mv.i OPERATOR(pg_catalog.=) diff.i OR (mv.i IS NULL AND diff.i IS NULL)) RETURNING mv.i) INSERT INTO public.mv_ivm_agg (i, sum, count, avg, __ivm_count_sum__, __ivm_count_avg__, __ivm_sum_avg__, __ivm_count__) SELECT i, sum, count, avg, __ivm_count_sum__, __ivm_count_avg__, __ivm_sum_avg__, __ivm_count__ FROM new_delta AS diff WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE (mv.i OPERATOR(pg_catalog.=) diff.i OR (mv.i IS NULL AND diff.i IS NULL))); At this final SPI_exec, we have a update statement with related columns { __ivm_count_sum__, sum, __ivm_count__, count, avg, __ivm_sum_avg__, __ivm_count_avg__}. At this time, my mind stops working, querybuf.data is way too big, but I still feel like there is some logic associated with these columns, maybe we can use it as an assertion to prove that this query (querybuf.len = 1834) is indeed correct. Since the apply delta query is quite complex, I feel like adding some "if debug then print out the final querybuf.data end if" would be a good idea. we add hidden columns somewhere, also to avoid corner cases, s
Re: Incremental View Maintenance, take 2
On Sun, 2 Jul 2023 10:38:20 +0800 jian he wrote: > ok. Now I really found a small bug. > > this works as intended: > BEGIN; > CREATE INCREMENTAL MATERIALIZED VIEW test_ivm AS SELECT i, MIN(j) as > min_j FROM mv_base_a group by 1; > INSERT INTO mv_base_a select 1,-2 where false; > rollback; > > however the following one: > BEGIN; > CREATE INCREMENTAL MATERIALIZED VIEW test_ivm1 AS SELECT MIN(j) as > min_j FROM mv_base_a; > INSERT INTO mv_base_a select 1, -2 where false; > rollback; > > will evaluate > tuplestore_tuple_count(new_tuplestores) to 1, it will walk through > IVM_immediate_maintenance function to apply_delta. > but should it be zero? This is not a bug because an aggregate without GROUP BY always results one row whose value is NULL. The contents of test_imv1 would be always same as " SELECT MIN(j) as min_j FROM mv_base_a;", isn't it? Regards, Yugo Nagata -- Yugo NAGATA
Re: Incremental View Maintenance, take 2
On Sun, 2 Jul 2023 08:25:12 +0800 jian he wrote: > This is probably not trivial. > In function apply_new_delta_with_count. > > appendStringInfo(&querybuf, > "WITH updt AS (" /* update a tuple if this exists in the view */ > "UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.+) diff.%s " > "%s " /* SET clauses for aggregates */ > "FROM %s AS diff " > "WHERE %s " /* tuple matching condition */ > "RETURNING %s" /* returning keys of updated tuples */ > ") INSERT INTO %s (%s)" /* insert a new tuple if this doesn't existw */ > "SELECT %s FROM %s AS diff " > "WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE %s);", > > - > ") INSERT INTO %s (%s)" /* insert a new tuple if this doesn't existw */ > "SELECT %s FROM %s AS diff " > > the INSERT INTO line, should have one white space in the end? > also "existw" should be "exists" Yes, we should need a space although it works. I'll fix as well as the typo. Thank you. Regards, Yugo Nagata -- Yugo NAGATA
Re: Incremental View Maintenance, take 2
On Fri, 30 Jun 2023 08:00:00 +0800 jian he wrote: > Hi there. > in v28-0005-Add-Incremental-View-Maintenance-support-to-psql.patch > I don't know how to set psql to get the output > "Incremental view maintenance: yes" This information will appear when you use "d+" command for an incrementally maintained materialized view. Regards, Yugo Nagata -- Yugo NAGATA
Re: Incremental View Maintenance, take 2
On Thu, 29 Jun 2023 18:51:06 +0800 jian he wrote: > I cannot build the doc. > git clean -fdx > git am ~/Desktop/tmp/*.patch > > Applying: Add a syntax to create Incrementally Maintainable Materialized Views > Applying: Add relisivm column to pg_class system catalog > Applying: Allow to prolong life span of transition tables until transaction > end > Applying: Add Incremental View Maintenance support to pg_dump > Applying: Add Incremental View Maintenance support to psql > Applying: Add Incremental View Maintenance support > Applying: Add DISTINCT support for IVM > Applying: Add aggregates support in IVM > Applying: Add support for min/max aggregates for IVM > Applying: Add regression tests for Incremental View Maintenance > Applying: Add documentations about Incremental View Maintenance > .git/rebase-apply/patch:79: trailing whitespace. > clause. > warning: 1 line adds whitespace errors. > > Because of this, the {ninja docs} command failed. ERROR message: > > [6/6] Generating doc/src/sgml/html with a custom command > FAILED: doc/src/sgml/html > /usr/bin/python3 > ../../Desktop/pg_sources/main/postgres/doc/src/sgml/xmltools_dep_wrapper > --targetname doc/src/sgml/html --depfile doc/src/sgml/html.d --tool > /usr/bin/xsltproc -- -o doc/src/sgml/ --nonet --stringparam pg.version > 16beta2 --path doc/src/sgml --path > ../../Desktop/pg_sources/main/postgres/doc/src/sgml > ../../Desktop/pg_sources/main/postgres/doc/src/sgml/stylesheet.xsl > doc/src/sgml/postgres-full.xml > ERROR: id attribute missing on element under /book[@id = > 'postgres']/part[@id = 'server-programming']/chapter[@id = > 'rules']/sect1[@id = 'rules-ivm'] > error: file doc/src/sgml/postgres-full.xml > xsltRunStylesheet : run failed > ninja: build stopped: subcommand failed. Thank your for pointing out this. I'll add ids for all sections to suppress the errors. Regards, Yugo Nagata -- Yugo NAGATA
Re: Incremental View Maintenance, take 2
On Thu, 29 Jun 2023 18:20:32 +0800 jian he wrote: > On Thu, Jun 29, 2023 at 12:40 AM jian he wrote: > > > > On Wed, Jun 28, 2023 at 4:06 PM Yugo NAGATA wrote: > > > > > > On Wed, 28 Jun 2023 00:01:02 +0800 > > > jian he wrote: > > > > > > > On Thu, Jun 1, 2023 at 2:47 AM Yugo NAGATA wrote: > > > > > > > > > > On Thu, 1 Jun 2023 23:59:09 +0900 > > > > > Yugo NAGATA wrote: > > > > > > > > > > > Hello hackers, > > > > > > > > > > > > Here's a rebased version of the patch-set adding Incremental View > > > > > > Maintenance support for PostgreSQL. That was discussed in [1]. > > > > > > > > > > > [1] > > > > > > https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp > > > > > > > > > > --- > > > > > * Overview > > > > > > > > > > Incremental View Maintenance (IVM) is a way to make materialized views > > > > > up-to-date by computing only incremental changes and applying them on > > > > > views. IVM is more efficient than REFRESH MATERIALIZED VIEW when > > > > > only small parts of the view are changed. > > > > > > > > > > ** Feature > > > > > > > > > > The attached patchset provides a feature that allows materialized > > > > > views > > > > > to be updated automatically and incrementally just after a underlying > > > > > table is modified. > > > > > > > > > > You can create an incementally maintainable materialized view (IMMV) > > > > > by using CREATE INCREMENTAL MATERIALIZED VIEW command. > > > > > > > > > > The followings are supported in view definition queries: > > > > > - SELECT ... FROM ... WHERE ..., joins (inner joins, self-joins) > > > > > - some built-in aggregate functions (count, sum, avg, min, max) > > > > > - GROUP BY clause > > > > > - DISTINCT clause > > > > > > > > > > Views can contain multiple tuples with the same content (duplicate > > > > > tuples). > > > > > > > > > > ** Restriction > > > > > > > > > > The following are not supported in a view definition: > > > > > - Outer joins > > > > > - Aggregates otehr than above, window functions, HAVING > > > > > - Sub-queries, CTEs > > > > > - Set operations (UNION, INTERSECT, EXCEPT) > > > > > - DISTINCT ON, ORDER BY, LIMIT, OFFSET > > > > > > > > > > Also, a view definition query cannot contain other views, > > > > > materialized views, > > > > > foreign tables, partitioned tables, partitions, VALUES, non-immutable > > > > > functions, > > > > > system columns, or expressions that contains aggregates. > > > > > > > > > > --- > > > > > * Design > > > > > > > > > > An IMMV is maintained using statement-level AFTER triggers. > > > > > When an IMMV is created, triggers are automatically created on all > > > > > base > > > > > tables contained in the view definition query. > > > > > > > > > > When a table is modified, changes that occurred in the table are > > > > > extracted > > > > > as transition tables in the AFTER triggers. Then, changes that will > > > > > occur in > > > > > the view are calculated by a rewritten view dequery in which the > > > > > modified table > > > > > is replaced with the transition table. > > > > > > > > > > For example, if the view is defined as "SELECT * FROM R, S", and > > > > > tuples inserted > > > > > into R are stored in a transiton table dR, the tuples that will be > > > > > inserted into > > > > > the view are calculated as the result of "SELECT * FROM dR, S". > > > > > > > > > > ** Multiple Tables Modification > > > > > > > > > > Multiple tables can be modified in a statement when using triggers, > > > > > foreign key > > > > > constraint, or modifying CTEs. When multiple tables are modified, we > > > > > need > > > > > the state of tables before the modification. > > > > > > > > > > For example, when some tuples, dR and dS, are inserted into R and S > > > > > respectively, > > > > > the tuples that will be inserted into the view are calculated by the > > > > > following > > > > > two queries: > > > > > > > > > > "SELECT * FROM dR, S_pre" > > > > > "SELECT * FROM R, dS" > > > > > > > > > > where S_pre is the table before the modification, R is the current > > > > > state of > > > > > table, that is, after the modification. This pre-update states of > > > > > table > > > > > is calculated by filtering inserted tuples and appending deleted > > > > > tuples. > > > > > The subquery that represents pre-update state is generated in > > > > > get_prestate_rte(). > > > > > Specifically, the insterted tuples are filtered by calling > > > > > IVM_visible_in_prestate() > > > > > in WHERE clause. This function checks the visibility of tuples by > > > > > using > > > > > the snapshot taken before table modification. The deleted tuples are > > > > > contained > > > > > in the old transition table, and this table is appended using UNION > > > > > ALL. > > > > > > > > > > Transition tables
Re: Incremental View Maintenance, take 2
On Thu, 29 Jun 2023 00:40:45 +0800 jian he wrote: > On Wed, Jun 28, 2023 at 4:06 PM Yugo NAGATA wrote: > > > > On Wed, 28 Jun 2023 00:01:02 +0800 > > jian he wrote: > > > > > On Thu, Jun 1, 2023 at 2:47 AM Yugo NAGATA wrote: > > > > > > > > On Thu, 1 Jun 2023 23:59:09 +0900 > > > > Yugo NAGATA wrote: > > > > > > > > > Hello hackers, > > > > > > > > > > Here's a rebased version of the patch-set adding Incremental View > > > > > Maintenance support for PostgreSQL. That was discussed in [1]. > > > > > > > > > [1] > > > > > https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp > > > > > > > > --- > > > > * Overview > > > > > > > > Incremental View Maintenance (IVM) is a way to make materialized views > > > > up-to-date by computing only incremental changes and applying them on > > > > views. IVM is more efficient than REFRESH MATERIALIZED VIEW when > > > > only small parts of the view are changed. > > > > > > > > ** Feature > > > > > > > > The attached patchset provides a feature that allows materialized views > > > > to be updated automatically and incrementally just after a underlying > > > > table is modified. > > > > > > > > You can create an incementally maintainable materialized view (IMMV) > > > > by using CREATE INCREMENTAL MATERIALIZED VIEW command. > > > > > > > > The followings are supported in view definition queries: > > > > - SELECT ... FROM ... WHERE ..., joins (inner joins, self-joins) > > > > - some built-in aggregate functions (count, sum, avg, min, max) > > > > - GROUP BY clause > > > > - DISTINCT clause > > > > > > > > Views can contain multiple tuples with the same content (duplicate > > > > tuples). > > > > > > > > ** Restriction > > > > > > > > The following are not supported in a view definition: > > > > - Outer joins > > > > - Aggregates otehr than above, window functions, HAVING > > > > - Sub-queries, CTEs > > > > - Set operations (UNION, INTERSECT, EXCEPT) > > > > - DISTINCT ON, ORDER BY, LIMIT, OFFSET > > > > > > > > Also, a view definition query cannot contain other views, materialized > > > > views, > > > > foreign tables, partitioned tables, partitions, VALUES, non-immutable > > > > functions, > > > > system columns, or expressions that contains aggregates. > > > > > > > > --- > > > > * Design > > > > > > > > An IMMV is maintained using statement-level AFTER triggers. > > > > When an IMMV is created, triggers are automatically created on all base > > > > tables contained in the view definition query. > > > > > > > > When a table is modified, changes that occurred in the table are > > > > extracted > > > > as transition tables in the AFTER triggers. Then, changes that will > > > > occur in > > > > the view are calculated by a rewritten view dequery in which the > > > > modified table > > > > is replaced with the transition table. > > > > > > > > For example, if the view is defined as "SELECT * FROM R, S", and tuples > > > > inserted > > > > into R are stored in a transiton table dR, the tuples that will be > > > > inserted into > > > > the view are calculated as the result of "SELECT * FROM dR, S". > > > > > > > > ** Multiple Tables Modification > > > > > > > > Multiple tables can be modified in a statement when using triggers, > > > > foreign key > > > > constraint, or modifying CTEs. When multiple tables are modified, we > > > > need > > > > the state of tables before the modification. > > > > > > > > For example, when some tuples, dR and dS, are inserted into R and S > > > > respectively, > > > > the tuples that will be inserted into the view are calculated by the > > > > following > > > > two queries: > > > > > > > > "SELECT * FROM dR, S_pre" > > > > "SELECT * FROM R, dS" > > > > > > > > where S_pre is the table before the modification, R is the current > > > > state of > > > > table, that is, after the modification. This pre-update states of table > > > > is calculated by filtering inserted tuples and appending deleted tuples. > > > > The subquery that represents pre-update state is generated in > > > > get_prestate_rte(). > > > > Specifically, the insterted tuples are filtered by calling > > > > IVM_visible_in_prestate() > > > > in WHERE clause. This function checks the visibility of tuples by using > > > > the snapshot taken before table modification. The deleted tuples are > > > > contained > > > > in the old transition table, and this table is appended using UNION ALL. > > > > > > > > Transition tables for each modification are collected in each AFTER > > > > trigger > > > > function call. Then, the view maintenance is performed in the last call > > > > of > > > > the trigger. > > > > > > > > In the original PostgreSQL, tuplestores of transition tables are freed > > > > at the > > > > end of each nested query. However, their lifespan ne
Re: Incremental View Maintenance, take 2
ok. Now I really found a small bug. this works as intended: BEGIN; CREATE INCREMENTAL MATERIALIZED VIEW test_ivm AS SELECT i, MIN(j) as min_j FROM mv_base_a group by 1; INSERT INTO mv_base_a select 1,-2 where false; rollback; however the following one: BEGIN; CREATE INCREMENTAL MATERIALIZED VIEW test_ivm1 AS SELECT MIN(j) as min_j FROM mv_base_a; INSERT INTO mv_base_a select 1, -2 where false; rollback; will evaluate tuplestore_tuple_count(new_tuplestores) to 1, it will walk through IVM_immediate_maintenance function to apply_delta. but should it be zero?
Re: Incremental View Maintenance, take 2
This is probably not trivial. In function apply_new_delta_with_count. appendStringInfo(&querybuf, "WITH updt AS (" /* update a tuple if this exists in the view */ "UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.+) diff.%s " "%s " /* SET clauses for aggregates */ "FROM %s AS diff " "WHERE %s " /* tuple matching condition */ "RETURNING %s" /* returning keys of updated tuples */ ") INSERT INTO %s (%s)" /* insert a new tuple if this doesn't existw */ "SELECT %s FROM %s AS diff " "WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE %s);", - ") INSERT INTO %s (%s)" /* insert a new tuple if this doesn't existw */ "SELECT %s FROM %s AS diff " the INSERT INTO line, should have one white space in the end? also "existw" should be "exists"
Re: Incremental View Maintenance, take 2
Hi there. in v28-0005-Add-Incremental-View-Maintenance-support-to-psql.patch I don't know how to set psql to get the output "Incremental view maintenance: yes"
Re: Incremental View Maintenance, take 2
On Thu, Jun 29, 2023 at 6:51 PM jian he wrote: > > I cannot build the doc. > git clean -fdx > git am ~/Desktop/tmp/*.patch > > Applying: Add a syntax to create Incrementally Maintainable Materialized Views > Applying: Add relisivm column to pg_class system catalog > Applying: Allow to prolong life span of transition tables until transaction > end > Applying: Add Incremental View Maintenance support to pg_dump > Applying: Add Incremental View Maintenance support to psql > Applying: Add Incremental View Maintenance support > Applying: Add DISTINCT support for IVM > Applying: Add aggregates support in IVM > Applying: Add support for min/max aggregates for IVM > Applying: Add regression tests for Incremental View Maintenance > Applying: Add documentations about Incremental View Maintenance > .git/rebase-apply/patch:79: trailing whitespace. > clause. > warning: 1 line adds whitespace errors. > > Because of this, the {ninja docs} command failed. ERROR message: > > [6/6] Generating doc/src/sgml/html with a custom command > FAILED: doc/src/sgml/html > /usr/bin/python3 > ../../Desktop/pg_sources/main/postgres/doc/src/sgml/xmltools_dep_wrapper > --targetname doc/src/sgml/html --depfile doc/src/sgml/html.d --tool > /usr/bin/xsltproc -- -o doc/src/sgml/ --nonet --stringparam pg.version > 16beta2 --path doc/src/sgml --path > ../../Desktop/pg_sources/main/postgres/doc/src/sgml > ../../Desktop/pg_sources/main/postgres/doc/src/sgml/stylesheet.xsl > doc/src/sgml/postgres-full.xml > ERROR: id attribute missing on element under /book[@id = > 'postgres']/part[@id = 'server-programming']/chapter[@id = > 'rules']/sect1[@id = 'rules-ivm'] > error: file doc/src/sgml/postgres-full.xml > xsltRunStylesheet : run failed > ninja: build stopped: subcommand failed. so far what I tried: git am --ignore-whitespace --whitespace=nowarn ~/Desktop/tmp/*.patch git am --whitespace=fix ~/Desktop/tmp/*.patch git am --whitespace=error ~/Desktop/tmp/*.patch I still cannot generate html docs.
Re: Incremental View Maintenance, take 2
I cannot build the doc. git clean -fdx git am ~/Desktop/tmp/*.patch Applying: Add a syntax to create Incrementally Maintainable Materialized Views Applying: Add relisivm column to pg_class system catalog Applying: Allow to prolong life span of transition tables until transaction end Applying: Add Incremental View Maintenance support to pg_dump Applying: Add Incremental View Maintenance support to psql Applying: Add Incremental View Maintenance support Applying: Add DISTINCT support for IVM Applying: Add aggregates support in IVM Applying: Add support for min/max aggregates for IVM Applying: Add regression tests for Incremental View Maintenance Applying: Add documentations about Incremental View Maintenance .git/rebase-apply/patch:79: trailing whitespace. clause. warning: 1 line adds whitespace errors. Because of this, the {ninja docs} command failed. ERROR message: [6/6] Generating doc/src/sgml/html with a custom command FAILED: doc/src/sgml/html /usr/bin/python3 ../../Desktop/pg_sources/main/postgres/doc/src/sgml/xmltools_dep_wrapper --targetname doc/src/sgml/html --depfile doc/src/sgml/html.d --tool /usr/bin/xsltproc -- -o doc/src/sgml/ --nonet --stringparam pg.version 16beta2 --path doc/src/sgml --path ../../Desktop/pg_sources/main/postgres/doc/src/sgml ../../Desktop/pg_sources/main/postgres/doc/src/sgml/stylesheet.xsl doc/src/sgml/postgres-full.xml ERROR: id attribute missing on element under /book[@id = 'postgres']/part[@id = 'server-programming']/chapter[@id = 'rules']/sect1[@id = 'rules-ivm'] error: file doc/src/sgml/postgres-full.xml xsltRunStylesheet : run failed ninja: build stopped: subcommand failed.
Re: Incremental View Maintenance, take 2
On Thu, Jun 29, 2023 at 12:40 AM jian he wrote: > > On Wed, Jun 28, 2023 at 4:06 PM Yugo NAGATA wrote: > > > > On Wed, 28 Jun 2023 00:01:02 +0800 > > jian he wrote: > > > > > On Thu, Jun 1, 2023 at 2:47 AM Yugo NAGATA wrote: > > > > > > > > On Thu, 1 Jun 2023 23:59:09 +0900 > > > > Yugo NAGATA wrote: > > > > > > > > > Hello hackers, > > > > > > > > > > Here's a rebased version of the patch-set adding Incremental View > > > > > Maintenance support for PostgreSQL. That was discussed in [1]. > > > > > > > > > [1] > > > > > https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp > > > > > > > > --- > > > > * Overview > > > > > > > > Incremental View Maintenance (IVM) is a way to make materialized views > > > > up-to-date by computing only incremental changes and applying them on > > > > views. IVM is more efficient than REFRESH MATERIALIZED VIEW when > > > > only small parts of the view are changed. > > > > > > > > ** Feature > > > > > > > > The attached patchset provides a feature that allows materialized views > > > > to be updated automatically and incrementally just after a underlying > > > > table is modified. > > > > > > > > You can create an incementally maintainable materialized view (IMMV) > > > > by using CREATE INCREMENTAL MATERIALIZED VIEW command. > > > > > > > > The followings are supported in view definition queries: > > > > - SELECT ... FROM ... WHERE ..., joins (inner joins, self-joins) > > > > - some built-in aggregate functions (count, sum, avg, min, max) > > > > - GROUP BY clause > > > > - DISTINCT clause > > > > > > > > Views can contain multiple tuples with the same content (duplicate > > > > tuples). > > > > > > > > ** Restriction > > > > > > > > The following are not supported in a view definition: > > > > - Outer joins > > > > - Aggregates otehr than above, window functions, HAVING > > > > - Sub-queries, CTEs > > > > - Set operations (UNION, INTERSECT, EXCEPT) > > > > - DISTINCT ON, ORDER BY, LIMIT, OFFSET > > > > > > > > Also, a view definition query cannot contain other views, materialized > > > > views, > > > > foreign tables, partitioned tables, partitions, VALUES, non-immutable > > > > functions, > > > > system columns, or expressions that contains aggregates. > > > > > > > > --- > > > > * Design > > > > > > > > An IMMV is maintained using statement-level AFTER triggers. > > > > When an IMMV is created, triggers are automatically created on all base > > > > tables contained in the view definition query. > > > > > > > > When a table is modified, changes that occurred in the table are > > > > extracted > > > > as transition tables in the AFTER triggers. Then, changes that will > > > > occur in > > > > the view are calculated by a rewritten view dequery in which the > > > > modified table > > > > is replaced with the transition table. > > > > > > > > For example, if the view is defined as "SELECT * FROM R, S", and tuples > > > > inserted > > > > into R are stored in a transiton table dR, the tuples that will be > > > > inserted into > > > > the view are calculated as the result of "SELECT * FROM dR, S". > > > > > > > > ** Multiple Tables Modification > > > > > > > > Multiple tables can be modified in a statement when using triggers, > > > > foreign key > > > > constraint, or modifying CTEs. When multiple tables are modified, we > > > > need > > > > the state of tables before the modification. > > > > > > > > For example, when some tuples, dR and dS, are inserted into R and S > > > > respectively, > > > > the tuples that will be inserted into the view are calculated by the > > > > following > > > > two queries: > > > > > > > > "SELECT * FROM dR, S_pre" > > > > "SELECT * FROM R, dS" > > > > > > > > where S_pre is the table before the modification, R is the current > > > > state of > > > > table, that is, after the modification. This pre-update states of table > > > > is calculated by filtering inserted tuples and appending deleted tuples. > > > > The subquery that represents pre-update state is generated in > > > > get_prestate_rte(). > > > > Specifically, the insterted tuples are filtered by calling > > > > IVM_visible_in_prestate() > > > > in WHERE clause. This function checks the visibility of tuples by using > > > > the snapshot taken before table modification. The deleted tuples are > > > > contained > > > > in the old transition table, and this table is appended using UNION ALL. > > > > > > > > Transition tables for each modification are collected in each AFTER > > > > trigger > > > > function call. Then, the view maintenance is performed in the last call > > > > of > > > > the trigger. > > > > > > > > In the original PostgreSQL, tuplestores of transition tables are freed > > > > at the > > > > end of each nested query. However, their lifespan nee
Re: Incremental View Maintenance, take 2
On Wed, Jun 28, 2023 at 4:06 PM Yugo NAGATA wrote: > > On Wed, 28 Jun 2023 00:01:02 +0800 > jian he wrote: > > > On Thu, Jun 1, 2023 at 2:47 AM Yugo NAGATA wrote: > > > > > > On Thu, 1 Jun 2023 23:59:09 +0900 > > > Yugo NAGATA wrote: > > > > > > > Hello hackers, > > > > > > > > Here's a rebased version of the patch-set adding Incremental View > > > > Maintenance support for PostgreSQL. That was discussed in [1]. > > > > > > > [1] > > > > https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp > > > > > > --- > > > * Overview > > > > > > Incremental View Maintenance (IVM) is a way to make materialized views > > > up-to-date by computing only incremental changes and applying them on > > > views. IVM is more efficient than REFRESH MATERIALIZED VIEW when > > > only small parts of the view are changed. > > > > > > ** Feature > > > > > > The attached patchset provides a feature that allows materialized views > > > to be updated automatically and incrementally just after a underlying > > > table is modified. > > > > > > You can create an incementally maintainable materialized view (IMMV) > > > by using CREATE INCREMENTAL MATERIALIZED VIEW command. > > > > > > The followings are supported in view definition queries: > > > - SELECT ... FROM ... WHERE ..., joins (inner joins, self-joins) > > > - some built-in aggregate functions (count, sum, avg, min, max) > > > - GROUP BY clause > > > - DISTINCT clause > > > > > > Views can contain multiple tuples with the same content (duplicate > > > tuples). > > > > > > ** Restriction > > > > > > The following are not supported in a view definition: > > > - Outer joins > > > - Aggregates otehr than above, window functions, HAVING > > > - Sub-queries, CTEs > > > - Set operations (UNION, INTERSECT, EXCEPT) > > > - DISTINCT ON, ORDER BY, LIMIT, OFFSET > > > > > > Also, a view definition query cannot contain other views, materialized > > > views, > > > foreign tables, partitioned tables, partitions, VALUES, non-immutable > > > functions, > > > system columns, or expressions that contains aggregates. > > > > > > --- > > > * Design > > > > > > An IMMV is maintained using statement-level AFTER triggers. > > > When an IMMV is created, triggers are automatically created on all base > > > tables contained in the view definition query. > > > > > > When a table is modified, changes that occurred in the table are extracted > > > as transition tables in the AFTER triggers. Then, changes that will occur > > > in > > > the view are calculated by a rewritten view dequery in which the modified > > > table > > > is replaced with the transition table. > > > > > > For example, if the view is defined as "SELECT * FROM R, S", and tuples > > > inserted > > > into R are stored in a transiton table dR, the tuples that will be > > > inserted into > > > the view are calculated as the result of "SELECT * FROM dR, S". > > > > > > ** Multiple Tables Modification > > > > > > Multiple tables can be modified in a statement when using triggers, > > > foreign key > > > constraint, or modifying CTEs. When multiple tables are modified, we need > > > the state of tables before the modification. > > > > > > For example, when some tuples, dR and dS, are inserted into R and S > > > respectively, > > > the tuples that will be inserted into the view are calculated by the > > > following > > > two queries: > > > > > > "SELECT * FROM dR, S_pre" > > > "SELECT * FROM R, dS" > > > > > > where S_pre is the table before the modification, R is the current state > > > of > > > table, that is, after the modification. This pre-update states of table > > > is calculated by filtering inserted tuples and appending deleted tuples. > > > The subquery that represents pre-update state is generated in > > > get_prestate_rte(). > > > Specifically, the insterted tuples are filtered by calling > > > IVM_visible_in_prestate() > > > in WHERE clause. This function checks the visibility of tuples by using > > > the snapshot taken before table modification. The deleted tuples are > > > contained > > > in the old transition table, and this table is appended using UNION ALL. > > > > > > Transition tables for each modification are collected in each AFTER > > > trigger > > > function call. Then, the view maintenance is performed in the last call of > > > the trigger. > > > > > > In the original PostgreSQL, tuplestores of transition tables are freed at > > > the > > > end of each nested query. However, their lifespan needs to be prolonged to > > > the end of the out-most query in order to maintain the view in the last > > > AFTER > > > trigger. For this purpose, SetTransitionTablePreserved is added in > > > trigger.c. > > > > > > ** Duplicate Tulpes > > > > > > When calculating changes that will occur in the view (= delta
Re: Incremental View Maintenance, take 2
On Wed, 28 Jun 2023 00:01:02 +0800 jian he wrote: > On Thu, Jun 1, 2023 at 2:47 AM Yugo NAGATA wrote: > > > > On Thu, 1 Jun 2023 23:59:09 +0900 > > Yugo NAGATA wrote: > > > > > Hello hackers, > > > > > > Here's a rebased version of the patch-set adding Incremental View > > > Maintenance support for PostgreSQL. That was discussed in [1]. > > > > > [1] > > > https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp > > > > --- > > * Overview > > > > Incremental View Maintenance (IVM) is a way to make materialized views > > up-to-date by computing only incremental changes and applying them on > > views. IVM is more efficient than REFRESH MATERIALIZED VIEW when > > only small parts of the view are changed. > > > > ** Feature > > > > The attached patchset provides a feature that allows materialized views > > to be updated automatically and incrementally just after a underlying > > table is modified. > > > > You can create an incementally maintainable materialized view (IMMV) > > by using CREATE INCREMENTAL MATERIALIZED VIEW command. > > > > The followings are supported in view definition queries: > > - SELECT ... FROM ... WHERE ..., joins (inner joins, self-joins) > > - some built-in aggregate functions (count, sum, avg, min, max) > > - GROUP BY clause > > - DISTINCT clause > > > > Views can contain multiple tuples with the same content (duplicate tuples). > > > > ** Restriction > > > > The following are not supported in a view definition: > > - Outer joins > > - Aggregates otehr than above, window functions, HAVING > > - Sub-queries, CTEs > > - Set operations (UNION, INTERSECT, EXCEPT) > > - DISTINCT ON, ORDER BY, LIMIT, OFFSET > > > > Also, a view definition query cannot contain other views, materialized > > views, > > foreign tables, partitioned tables, partitions, VALUES, non-immutable > > functions, > > system columns, or expressions that contains aggregates. > > > > --- > > * Design > > > > An IMMV is maintained using statement-level AFTER triggers. > > When an IMMV is created, triggers are automatically created on all base > > tables contained in the view definition query. > > > > When a table is modified, changes that occurred in the table are extracted > > as transition tables in the AFTER triggers. Then, changes that will occur in > > the view are calculated by a rewritten view dequery in which the modified > > table > > is replaced with the transition table. > > > > For example, if the view is defined as "SELECT * FROM R, S", and tuples > > inserted > > into R are stored in a transiton table dR, the tuples that will be inserted > > into > > the view are calculated as the result of "SELECT * FROM dR, S". > > > > ** Multiple Tables Modification > > > > Multiple tables can be modified in a statement when using triggers, foreign > > key > > constraint, or modifying CTEs. When multiple tables are modified, we need > > the state of tables before the modification. > > > > For example, when some tuples, dR and dS, are inserted into R and S > > respectively, > > the tuples that will be inserted into the view are calculated by the > > following > > two queries: > > > > "SELECT * FROM dR, S_pre" > > "SELECT * FROM R, dS" > > > > where S_pre is the table before the modification, R is the current state of > > table, that is, after the modification. This pre-update states of table > > is calculated by filtering inserted tuples and appending deleted tuples. > > The subquery that represents pre-update state is generated in > > get_prestate_rte(). > > Specifically, the insterted tuples are filtered by calling > > IVM_visible_in_prestate() > > in WHERE clause. This function checks the visibility of tuples by using > > the snapshot taken before table modification. The deleted tuples are > > contained > > in the old transition table, and this table is appended using UNION ALL. > > > > Transition tables for each modification are collected in each AFTER trigger > > function call. Then, the view maintenance is performed in the last call of > > the trigger. > > > > In the original PostgreSQL, tuplestores of transition tables are freed at > > the > > end of each nested query. However, their lifespan needs to be prolonged to > > the end of the out-most query in order to maintain the view in the last > > AFTER > > trigger. For this purpose, SetTransitionTablePreserved is added in > > trigger.c. > > > > ** Duplicate Tulpes > > > > When calculating changes that will occur in the view (= delta tables), > > multiplicity of tuples are calculated by using count(*). > > > > When deleting tuples from the view, tuples to be deleted are identified by > > joining the delta table with the view, and tuples are deleted as many as > > specified multiplicity by numbered using row_number() function. > > This is impleme
Re: Incremental View Maintenance, take 2
On Thu, Jun 1, 2023 at 2:47 AM Yugo NAGATA wrote: > > On Thu, 1 Jun 2023 23:59:09 +0900 > Yugo NAGATA wrote: > > > Hello hackers, > > > > Here's a rebased version of the patch-set adding Incremental View > > Maintenance support for PostgreSQL. That was discussed in [1]. > > > [1] > > https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp > > --- > * Overview > > Incremental View Maintenance (IVM) is a way to make materialized views > up-to-date by computing only incremental changes and applying them on > views. IVM is more efficient than REFRESH MATERIALIZED VIEW when > only small parts of the view are changed. > > ** Feature > > The attached patchset provides a feature that allows materialized views > to be updated automatically and incrementally just after a underlying > table is modified. > > You can create an incementally maintainable materialized view (IMMV) > by using CREATE INCREMENTAL MATERIALIZED VIEW command. > > The followings are supported in view definition queries: > - SELECT ... FROM ... WHERE ..., joins (inner joins, self-joins) > - some built-in aggregate functions (count, sum, avg, min, max) > - GROUP BY clause > - DISTINCT clause > > Views can contain multiple tuples with the same content (duplicate tuples). > > ** Restriction > > The following are not supported in a view definition: > - Outer joins > - Aggregates otehr than above, window functions, HAVING > - Sub-queries, CTEs > - Set operations (UNION, INTERSECT, EXCEPT) > - DISTINCT ON, ORDER BY, LIMIT, OFFSET > > Also, a view definition query cannot contain other views, materialized views, > foreign tables, partitioned tables, partitions, VALUES, non-immutable > functions, > system columns, or expressions that contains aggregates. > > --- > * Design > > An IMMV is maintained using statement-level AFTER triggers. > When an IMMV is created, triggers are automatically created on all base > tables contained in the view definition query. > > When a table is modified, changes that occurred in the table are extracted > as transition tables in the AFTER triggers. Then, changes that will occur in > the view are calculated by a rewritten view dequery in which the modified > table > is replaced with the transition table. > > For example, if the view is defined as "SELECT * FROM R, S", and tuples > inserted > into R are stored in a transiton table dR, the tuples that will be inserted > into > the view are calculated as the result of "SELECT * FROM dR, S". > > ** Multiple Tables Modification > > Multiple tables can be modified in a statement when using triggers, foreign > key > constraint, or modifying CTEs. When multiple tables are modified, we need > the state of tables before the modification. > > For example, when some tuples, dR and dS, are inserted into R and S > respectively, > the tuples that will be inserted into the view are calculated by the following > two queries: > > "SELECT * FROM dR, S_pre" > "SELECT * FROM R, dS" > > where S_pre is the table before the modification, R is the current state of > table, that is, after the modification. This pre-update states of table > is calculated by filtering inserted tuples and appending deleted tuples. > The subquery that represents pre-update state is generated in > get_prestate_rte(). > Specifically, the insterted tuples are filtered by calling > IVM_visible_in_prestate() > in WHERE clause. This function checks the visibility of tuples by using > the snapshot taken before table modification. The deleted tuples are contained > in the old transition table, and this table is appended using UNION ALL. > > Transition tables for each modification are collected in each AFTER trigger > function call. Then, the view maintenance is performed in the last call of > the trigger. > > In the original PostgreSQL, tuplestores of transition tables are freed at the > end of each nested query. However, their lifespan needs to be prolonged to > the end of the out-most query in order to maintain the view in the last AFTER > trigger. For this purpose, SetTransitionTablePreserved is added in trigger.c. > > ** Duplicate Tulpes > > When calculating changes that will occur in the view (= delta tables), > multiplicity of tuples are calculated by using count(*). > > When deleting tuples from the view, tuples to be deleted are identified by > joining the delta table with the view, and tuples are deleted as many as > specified multiplicity by numbered using row_number() function. > This is implemented in apply_old_delta(). > > When inserting tuples into the view, each tuple is duplicated to the > specified multiplicity using generate_series() function. This is implemented > in apply_new_delta(). > > ** DISTINCT clause > > When DISTINCT is used, the view has a hidden column __ivm_count__ that > stores mu
Re: Incremental View Maintenance, take 2
On Thu, 1 Jun 2023 23:59:09 +0900 Yugo NAGATA wrote: > Hello hackers, > > Here's a rebased version of the patch-set adding Incremental View > Maintenance support for PostgreSQL. That was discussed in [1]. > [1] > https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp --- * Overview Incremental View Maintenance (IVM) is a way to make materialized views up-to-date by computing only incremental changes and applying them on views. IVM is more efficient than REFRESH MATERIALIZED VIEW when only small parts of the view are changed. ** Feature The attached patchset provides a feature that allows materialized views to be updated automatically and incrementally just after a underlying table is modified. You can create an incementally maintainable materialized view (IMMV) by using CREATE INCREMENTAL MATERIALIZED VIEW command. The followings are supported in view definition queries: - SELECT ... FROM ... WHERE ..., joins (inner joins, self-joins) - some built-in aggregate functions (count, sum, avg, min, max) - GROUP BY clause - DISTINCT clause Views can contain multiple tuples with the same content (duplicate tuples). ** Restriction The following are not supported in a view definition: - Outer joins - Aggregates otehr than above, window functions, HAVING - Sub-queries, CTEs - Set operations (UNION, INTERSECT, EXCEPT) - DISTINCT ON, ORDER BY, LIMIT, OFFSET Also, a view definition query cannot contain other views, materialized views, foreign tables, partitioned tables, partitions, VALUES, non-immutable functions, system columns, or expressions that contains aggregates. --- * Design An IMMV is maintained using statement-level AFTER triggers. When an IMMV is created, triggers are automatically created on all base tables contained in the view definition query. When a table is modified, changes that occurred in the table are extracted as transition tables in the AFTER triggers. Then, changes that will occur in the view are calculated by a rewritten view dequery in which the modified table is replaced with the transition table. For example, if the view is defined as "SELECT * FROM R, S", and tuples inserted into R are stored in a transiton table dR, the tuples that will be inserted into the view are calculated as the result of "SELECT * FROM dR, S". ** Multiple Tables Modification Multiple tables can be modified in a statement when using triggers, foreign key constraint, or modifying CTEs. When multiple tables are modified, we need the state of tables before the modification. For example, when some tuples, dR and dS, are inserted into R and S respectively, the tuples that will be inserted into the view are calculated by the following two queries: "SELECT * FROM dR, S_pre" "SELECT * FROM R, dS" where S_pre is the table before the modification, R is the current state of table, that is, after the modification. This pre-update states of table is calculated by filtering inserted tuples and appending deleted tuples. The subquery that represents pre-update state is generated in get_prestate_rte(). Specifically, the insterted tuples are filtered by calling IVM_visible_in_prestate() in WHERE clause. This function checks the visibility of tuples by using the snapshot taken before table modification. The deleted tuples are contained in the old transition table, and this table is appended using UNION ALL. Transition tables for each modification are collected in each AFTER trigger function call. Then, the view maintenance is performed in the last call of the trigger. In the original PostgreSQL, tuplestores of transition tables are freed at the end of each nested query. However, their lifespan needs to be prolonged to the end of the out-most query in order to maintain the view in the last AFTER trigger. For this purpose, SetTransitionTablePreserved is added in trigger.c. ** Duplicate Tulpes When calculating changes that will occur in the view (= delta tables), multiplicity of tuples are calculated by using count(*). When deleting tuples from the view, tuples to be deleted are identified by joining the delta table with the view, and tuples are deleted as many as specified multiplicity by numbered using row_number() function. This is implemented in apply_old_delta(). When inserting tuples into the view, each tuple is duplicated to the specified multiplicity using generate_series() function. This is implemented in apply_new_delta(). ** DISTINCT clause When DISTINCT is used, the view has a hidden column __ivm_count__ that stores multiplicity for tuples. When tuples are deleted from or inserted into the view, the values of __ivm_count__ column is decreased or increased as many as specified multiplicity. Eventually, when the values becomes zero, the corresponding tuple is deleted from the v