public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Kirill Reshke <reshkekirill@gmail.com>
To: Yugo NAGATA <nagata@sraoss.co.jp>
Cc: Peter Smith <smithpb2250@gmail.com>
Cc: jian he <jian.universality@gmail.com>
Cc: Tatsuo Ishii <ishii@sraoss.co.jp>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Incremental View Maintenance, take 2
Date: Tue, 30 Jul 2024 03:32:19 +0500
Message-ID: <CALdSSPip9ruUoQMmsD_hQ0xY72qB=_jB-ayHeUWUH-dd0MB60A@mail.gmail.com> (raw)
In-Reply-To: <CALdSSPhj1H1NS7QiYkSQNCksPCwjtLcyt3==evgkBX1SrKyVdQ@mail.gmail.com>
References: <20230828115252.c1b018605b9a0756a30c3382@sraoss.co.jp>
<20230828160530.adde1e20f257d7d345989163@sraoss.co.jp>
<CACJufxEoCCJE1vntJp1SWjen8vBUa3vZLgL=swPwar4zim976g@mail.gmail.com>
<20230902.204634.955758704959569058.t-ishii@sranhm.sra.co.jp>
<CACJufxFjankFQDNppOfqCTpY=zW4Q0+2WCmKjT95kggiT978Lw@mail.gmail.com>
<CAHut+PsDpBTxZ7bLhko7_E-C7khMhoNJcriNQ_p_gWjADn01vg@mail.gmail.com>
<20240123162327.c2803162619dd7634cca0b6c@sraoss.co.jp>
<20240304115846.2275fb44fd904e8789d43590@sraoss.co.jp>
<20240329234700.73ff2e28c9248d29f8fa6a66@sraoss.co.jp>
<20240331225931.712683cecb26862b73b2b822@sraoss.co.jp>
<20240702170311.1ddb417759a48ff12c555b92@sranhm.sraoss.co.jp.sranhm>
<20240711132357.fe3f78c184cfa99159208178@sranhm.sraoss.co.jp>
<CALdSSPhj1H1NS7QiYkSQNCksPCwjtLcyt3==evgkBX1SrKyVdQ@mail.gmail.com>
On Sat, 27 Jul 2024 at 13:26, Kirill Reshke <reshkekirill@gmail.com> 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 <nagata@sraoss.co.jp> 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.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: reshkekirill@gmail.com, nagata@sraoss.co.jp, smithpb2250@gmail.com, jian.universality@gmail.com, ishii@sraoss.co.jp
Subject: Re: Incremental View Maintenance, take 2
In-Reply-To: <CALdSSPip9ruUoQMmsD_hQ0xY72qB=_jB-ayHeUWUH-dd0MB60A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox