Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oGQMB-00030P-Nm for pgsql-hackers@arkaria.postgresql.org; Tue, 26 Jul 2022 19:36:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oGQMA-00037F-Li for pgsql-hackers@arkaria.postgresql.org; Tue, 26 Jul 2022 19:36:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oGBkR-0000u7-2g for pgsql-hackers@lists.postgresql.org; Tue, 26 Jul 2022 04:00:44 +0000 Received: from out203-205-221-221.mail.qq.com ([203.205.221.221]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oGBkJ-0001l1-A3 for pgsql-hackers@lists.postgresql.org; Tue, 26 Jul 2022 04:00:41 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=qq.com; s=s201512; t=1658808029; bh=GDhyad+EjiWD0da/AFFbv5YCiThBPbwNsBXG4ZCItDo=; h=From:Subject:Date:In-Reply-To:Cc:To:References; b=eJYJTGpO6sSA0LQQJQjjY0voevGSyQH26H5C9M6RExj1wZClGCe47iixqE2vOOCpa zO4kC5zBXUXiwzo/Pw879k4oCzo1qwz74u/d9adsgufBna0YwOu5r1kJMYrNKK8snU BRsST4n3UcpZaxXnrlgrlZlgZJggKY7Z/HnZjkwM= Received: from smtpclient.apple ([42.120.75.253]) by newxmesmtplogicsvrsza7.qq.com (NewEsmtp) with SMTP id 1A9F23C; Tue, 26 Jul 2022 12:00:26 +0800 X-QQ-mid: xmsmtpt1658808026tgmuiddb5 Message-ID: X-QQ-XMAILINFO: MB5+LsFw85No98TaXg2V/B0G1t2niyTXX66v4gt7mZnqCVWDJBqJLY200nc5Yx 8gfDCyeMG6aDSQcEFYMbG6asE1GjvB8+T5/UjqcMjh188sM7L2001ZYd0Pam75DhJytc+/Cj+jPY I7Z4VDWdVgdyi1nVM0gyCswZYSzuQfUEFiKDF3LbednspP22QlbZMTf5eEu6YtigmV6VeszKbzmf u0Ztcucwqm6edFvMmhHxAmw5lIUM5jPkOuFPbpgMINAZ5za0IOAdZwaYSdblA16wp9rjL8KKCLj0 ODSSY3CoWdVmzV9fLcvnWFG52tHahX7q0xzYibnpqvrDsxdZBKrGQIor5KmBNUj5i6EmfBLmnn18 j0IbADq334pGoNlAyCSb0Hm2S9/Wa2d7WX4U531LErlhtKhf19PIBy3mPJtV1weAFtgAcZxrHutf 4GukOM4nvtScYL465yyxqAPiHW674Y//TnUNoIjtk0WcLaL5uN0ZDhQNNto6n22vEeIQafZbklUA I+LlxwNESi/OyL7mz3rps41g7Eub/crTWpu5KWmde9NAWqgHZ/KuU4lwvv3c8ZrOiCyeIMGrWXiR rBAXmSM3+FIZ2PPVpBYNyuRCLlspc5naP40mfnehEWW5xGEBbK6f2XGGQiS9AOO8NuTDA3hpV5cT SKL2K3OI+2PZLaEqjLwDPZwYcemIcquGFK+nlhvUqWX3VmfxMfnGeaLUPSFfsmcT8OpsAyVXvXAc zsk9jvnCTJRGSAMZerXaXQ2rh+MibVeLHX6kRIE2fRF+xrsuB8P610O6ffnwimWukQDQbgy1X6Sl W2ny4grNlAtSFd9mFiMtC3fT7NhM0v1Vj9d8GqesFvS5DKn95huLaga5kCOH03E52xtLZtxAFi6x OqYRrHluV7tlVvel75uk8MYSAokErHcBub2dMwBa/s6TlXU3OYyysui+IuHBlzsqTAFRl0fhdorx 6Amcr3xmU1DJbeObCYlDRC4i37sg5Y5hcsz2q4Zbky3QnscgACJ+Q/QK77w1v3CkRZkmpp2luPlu OIe6tQObQu24Z2LdcY From: huyajun X-OQ-MSGID: Content-Type: multipart/alternative; boundary="Apple-Mail=_9E56476E-9ECE-4850-8EF3-CB43D1D8E716" Mime-Version: 1.0 (Mac OS X Mail 15.0 \(3693.20.0.1.32\)) Subject: Re: Implementing Incremental View Maintenance Date: Tue, 26 Jul 2022 12:00:26 +0800 In-Reply-To: <20220708192211.a5a5210b13c2d1911b578a38@sraoss.co.jp> Cc: PostgreSQL Hackers , Greg Stark , Zhihong Yu , Julien Rouhaud , "r.takahashi_2@fujitsu.com" , vignesh C , Tom Lane , Andrew Dunstan , Andy Fan , Tatsuo Ishii , Thomas Munro To: Yugo NAGATA References: <20210517133646.04c3d9ed232af22c0f290ebc@sraoss.co.jp> <20210922191227.09a9891ec9da5ebe67475f1f@sraoss.co.jp> <20211125163710.2f32ae3d4be5d5f9ade020b6@sraoss.co.jp> <20220113102342.mlhknuy6ppypflq3@jrouhaud> <20220204012548.788837b8b3d76aa57022ba9c@sraoss.co.jp> <20220204014806.d29d5d923a0c51ad723cedb6@sraoss.co.jp> <20220314192616.8d15d05d71b1e426c4bfaf96@sraoss.co.jp> <20220422112939.dc2a3a4e559defc9b43024d0@sraoss.co.jp> <20220422145801.2a1ded1096bbe5cec983cfdc@sraoss.co.jp> <20220708192211.a5a5210b13c2d1911b578a38@sraoss.co.jp> X-Mailer: Apple Mail (2.3693.20.0.1.32) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_9E56476E-9ECE-4850-8EF3-CB43D1D8E716 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi, Nagata-san =09 Thank you for your answer, I agree with your opinion, and found some new = problems to discuss with you >=20 >> 3. Consider truncate base tables, IVM will not refresh, maybe raise = an error will be better >=20 > I fixed to support TRUNCATE on base tables in our repository. > = https://github.com/sraoss/pgsql-ivm/commit/a1365ed69f34e1adbd160f2ce8fd1e8= 0e032392f >=20 > When a base table is truncated, the view content will be empty if the > view definition query does not contain an aggregate without a GROUP = clause. > Therefore, such views can be truncated.=20 >=20 > Aggregate views without a GROUP clause always have one row. Therefore, > if a base table is truncated, the view will not be empty and will = contain > a row with NULL value (or 0 for count()). So, in this case, we refresh = the > view instead of truncating it. >=20 > The next version of the patch-set will include this change.=20 >=20 I read your patch and think this processing is greet, but there is a = risk of deadlock.=20 Although I have not thought of a suitable processing method for the time = being,=20 it is also acceptable for truncate scenarios.The deadlock scene is as = follows: Mv define is: select * from base_a,base_b; S1: truncate base_a; =E2=80=94 only AccessExclusiveLock base_a and not = run into after trigger S2: insert into base_b; =E2=80=94 The update has been completed and the = incremental refresh is started in the after trigger=EF=BC=8CRowExclusive = on base_b and ExclusiveLock on mv S1: continue truncate mv, wait for AccessExclusiveLock on mv, wait for = S2 S2: continue refresh mv, wait for AccessShardLock on base_a, wait for S1 So deadlock occurred I also found some new issues that I would like to discuss with you 1. Concurrent DML causes imv data error, case like below Setup: Create table t( a int); Insert into t select 1 from generate_series(1,3); create incremental materialized view s as select count(*) from t; S1: begin;delete from t where ctid in (select ctid from t limit 1); S2: begin;delete from t where ctid in (select ctid from t limit 1 offset = 1); S1: commit; S2: commit; After this, The count data of s becomes 2 but correct data is 1. I found out that the problem is probably because to our use of ctid = update Consider user behavior unrelated to imv=EF=BC=9A Create table t( a int); Insert into t select 1; s1: BEGIN s1: update t set a =3D 2 where ctid in (select ctid from t); -- UPDATE 1 s2: BEGIN s2: update t set a =3D 3 where ctid in (select ctid from t); -- wait row = lock s1: COMMIT s2: -- UPDATE 0 -- ctid change so can't UPDATE one rows So we lost the s2 update 2. Sometimes it will crash when the columns of the created materialized = view do not match Create table t( a int); create incremental materialized view s(z) as select sum(1) as a, sum(1) = as b from t; The problem should be that colNames in rewriteQueryForIMMV does not = consider this situation 3. Sometimes no error when the columns of the created materialized view = do not match Create table t( a int); create incremental materialized view s(y,z) as select count(1) as b = from t; But the hidden column of IMV is overwritten to z which will cause = refresh failed. The problem should be that checkRuleResultList we should only skip imv = hidden columns check 4. A unique index should not be created in the case of a Cartesian = product create table base_a (i int primary key, j varchar); create table base_b (i int primary key, k varchar); INSERT INTO base_a VALUES (1,10), (2,20), (3,30), (4,40), (5,50); INSERT INTO base_b VALUES (1,101), (2,102), (3,103), (4,104); CREATE incremental MATERIALIZED VIEW s as select base_a.i,base_a.j from base_a,base_b; =E2=80=94 create error = because of unique index 5. Besides, I would like to ask you if you have considered implementing = an IMV with delayed refresh? The advantage of delayed refresh is that it will not have much impact on = write performance I probably have some ideas about it now, do you think it works? 1. After the base table is updated, the delayed IMV's after trigger is = used to record the delta information in another table similar to the incremental log of the base = table 2. When incremental refresh, use the data in the log instead of the data = in the trasient table of the after trigger 3. We need to merge the incremental information in advance to ensure = that the base_table after transaction filtering UNION ALL old_delta is the state before the = base table is updated Case like below: Create table t( a int); =E2=80=94begin to record log Insert into t select 1; =E2=80=94 newlog: 1 oldlog: empty Delete from t; =E2=80=94newlog:1, oldlog=EF=BC=9A1 =E2=80=94 begin to incremental refresh Select * from t where xmin < xid or (xmin =3D xid and cmin < cid); =E2=80=94= empty So this union all oldlog is not equal to before the base table is = updated We need merge the incremental log in advance to make newlog: empty, = oldlog: empty If implemented, incremental refresh must still be serialized, but the = DML of the base table=20 can not be blocked, that is to say, the base table can still record logs = during incremental refresh, as long as we use same snapshot when incrementally updating. do you think there will be any problems with this solution? Looking forward to your reply to answer my above doubts, thank you a = lot! Regards, Yajun Hu= --Apple-Mail=_9E56476E-9ECE-4850-8EF3-CB43D1D8E716 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi, Nagata-san
=
Thank you for your answer, I agree with = your opinion, and found some new problems to discuss with you

3. = Consider truncate base tables, IVM will not refresh, maybe raise an = error will be better

I fixed = to support TRUNCATE on base tables in our repository.
https://github.com/sraoss/pgsql-ivm/commit/a1365ed69f34e1adbd16= 0f2ce8fd1e80e032392f

When a base table = is truncated, the view content will be empty if the
view = definition query does not contain an aggregate without a GROUP = clause.
Therefore, such views can be truncated.

Aggregate views without a GROUP clause always = have one row. Therefore,
if a base table is truncated, the = view will not be empty and will contain
a row with NULL = value (or 0 for count()). So, in this case, we refresh the
view instead of truncating it.

The= next version of the patch-set will include this change.

I read your = patch and think this processing is greet, but there is a risk of = deadlock. 
Although I have not thought of a = suitable processing method for the time being, 
it is also acceptable for truncate scenarios.The deadlock = scene is as follows:

Mv define is: select * from base_a,base_b;
S1: truncate base_a; =E2=80=94 only AccessExclusiveLock = base_a and not run into after trigger
S2: insert = into base_b; =E2=80=94 The update has been completed and the = incremental refresh is started in the after trigger=EF=BC=8CRowExclusive = on base_b and ExclusiveLock on mv
S1: continue = truncate mv, wait for AccessExclusiveLock on mv, wait for S2
S2: continue refresh mv, wait for AccessShardLock on base_a, = wait for S1
So deadlock occurred

I also found some new = issues that I would like to discuss with you
1. Concurrent DML causes imv data error, case like = below
Setup:
Create table t( a = int);
Insert into t select 1 from = generate_series(1,3);
create incremental = materialized view s  as select count(*) from t;

S1: begin;delete = from t where ctid in (select ctid from t limit 1);
S2: begin;delete from t where ctid in (select ctid from t = limit 1 offset 1);
S1: commit;
S2: commit;

After this, The count data of s becomes 2 but correct data is = 1.
I found out that the problem is probably because = to our use of ctid update
Consider user behavior = unrelated to imv=EF=BC=9A

Create table t( a int);
Insert into t select 1;
s1: BEGIN
s1: update t set a =3D 2 = where ctid in (select ctid from t); --&= nbsp;UPDATE 1
s2: BEGIN
s2: update t set a =3D 3 = where ctid in (select ctid from t); --&= nbsp;wait row lock
s1: COMMIT
s2: -- UPDATE 0 -- ctid change&nb= sp;so can't UPDATE one rows
So we lost the s2 update

2. Sometimes it will crash when the columns of the = created materialized view do not match
Create table t( a = int);
create incremental = materialized view s(z)  as select sum(1) as a, sum(1) as b from = t;

The problem = should be that colNames in rewriteQueryForIMMV does not consider this = situation

3. = Sometimes no error when the columns of the created materialized view do = not match
 Create table t( a = int);
 create incremental = materialized view s(y,z)  as select  count(1) as b from = t;

But = the hidden column of IMV is overwritten to z which will cause refresh = failed.

The = problem should be that checkRuleResultList we should only skip imv hidden columns = check

4. = A unique index should not be created in the case of a Cartesian = product

create table base_a (i int primary&nbs= p;key, j varchar);
create table base_b (i int primary&nbs= p;key, k varchar);
INSERT INTO base_a VALUES
(1,10),
(2,20),
(3,30),
(4,40),
(5,50);
INSERT INTO base_b VALUES
(1,101),
(2,102),
(3,103),
(4,104);
CREATE incremental MATERIALIZED VIEW s = ;as
select base_a.i,base_a.j from base_a,base_b; = =E2=80=94 create error because of unique index

5. Besides, I = would like to ask you if you have considered implementing an IMV with = delayed refresh?
The advantage of delayed refresh is that = it will not have much impact on write performance
I probably have some ideas about it now, do you think it = works?
1. After the base table is updated, = the delayed IMV's after trigger is used to record the delta
 information in another table similar to the incremental = log of the base table
2. When incremental refresh, = use the data in the log instead of the data in the trasient = table
of the after trigger
3. We need to merge the incremental information in = advance to ensure that the base_table
after transaction = filtering UNION ALL old_delta is the state before the base table is = updated
Case like below:
Create table t( a int);
=E2=80=94begin to = record log
Insert into t select 1; =E2=80=94 = newlog: 1 oldlog: empty
Delete from t; =E2=80=94newlog:1, = oldlog=EF=BC=9A1
=E2=80=94 begin to incremental = refresh
Select * from t where = xmin < xid or (xmin =3D xid and = ;cmin < cid); =E2=80=94 empty
So this union all = oldlog is not equal to  before the base table is updated
We need  merge the incremental log in advance to make = newlog: empty, oldlog: empty

If implemented, incremental refresh must still be serialized, = but the DML of the base table 
can not be blocked, = that is to say, the base table can still record logs during incremental = refresh,
as long as we use same snapshot when = incrementally updating.

do you think there will be any problems with this = solution?

Looking forward to your = reply to answer my above doubts, thank = you a lot!
Regards,
Yajun Hu
= --Apple-Mail=_9E56476E-9ECE-4850-8EF3-CB43D1D8E716--