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 1qFkuE-0005B2-I2 for pgsql-hackers@arkaria.postgresql.org; Sun, 02 Jul 2023 00:25:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qFkuB-000461-GY for pgsql-hackers@arkaria.postgresql.org; Sun, 02 Jul 2023 00:25:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qFkuB-00045s-7K for pgsql-hackers@lists.postgresql.org; Sun, 02 Jul 2023 00:25:31 +0000 Received: from mail-qk1-x72b.google.com ([2607:f8b0:4864:20::72b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qFku5-001euy-Rx for pgsql-hackers@postgresql.org; Sun, 02 Jul 2023 00:25:30 +0000 Received: by mail-qk1-x72b.google.com with SMTP id af79cd13be357-76547539775so248586585a.3 for ; Sat, 01 Jul 2023 17:25:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1688257524; x=1690849524; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=JxMg3OvskURyKH32V9+SeGV5+QtVASQUmVBKVng28Tc=; b=ePSjhZwi5/5t2AJxwljdN6mlVckYCmiLHJifcfc/1lGOnYSnDo6I18k61wQihPHqK8 gEf+NCF3grvbg86f5yYnK2VFGSsxuJ7+WmVq6hXNYcTyY67H7UlstgZGNpDZeGf/vLuk nF9obxrLXjI6y262FAvBJ3lpuHHSi7IH3TTDBs+J2nhQRrth8TalxH8qkmwgbHaLQqlP tKKfPy7iBkGBarn6GiADRp/UWPByD1Ry0eJc6UTAS3QNyRR/5gkQrGAoXBQBrFkQPplo /NknF0zaW4yUX7psgZypLMIlQDdZ5SSLIq/v9m4vwp9hGEqJrOwk3ULUoHdAri6iiym4 kquw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1688257524; x=1690849524; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=JxMg3OvskURyKH32V9+SeGV5+QtVASQUmVBKVng28Tc=; b=RU0Bhg4JyLJS39h9bjpfAZKYuzpm4el8On6EKcA5tDGgavMMkIeGOhAk+TGN0VyrX9 X2HS8blPHWgIMwmai42LJRutoBSY66GiXmOukw0MulIcRJai4soOU+HyEBHGNgkrU7ns q8gC+Pfn/bF+C+BAbMesXCP5dj3hd15K5LPCo2FRK7sDJtskTyXCWTk/PgD+eEKSaQyJ TBYKHtxKplkF3xVxCMOu9UWMUfqYOoZuUhTJeH1NXbdvaXXjQyf503obvnnJNcroqNPi wzlgx7eQgPHaf8iq9O7W/uufaAN6FsO5cLlwmV0mVp23KK6QEvtHC4+201BoyXLPfN/C k+2w== X-Gm-Message-State: AC+VfDxRGmRX0Sj+Mc1hgOKUsXp5PG3XX9iMr36Rd17CkPzQCfBMESQv mFeIL5qn3HhLaUo+LuWCmzmaQ2JK4Figvcn/TtI= X-Google-Smtp-Source: APBJJlGqFMqg/e8rCHrBjJwpzGNclHTJ589mlVobLEqUJZPiMUoc1B6cTSkLDixgO1ZREBoUsOmBTohseD+W5M/RcL0= X-Received: by 2002:a37:ea0b:0:b0:765:de3c:2d9a with SMTP id t11-20020a37ea0b000000b00765de3c2d9amr6364082qkj.51.1688257523920; Sat, 01 Jul 2023 17:25:23 -0700 (PDT) MIME-Version: 1.0 References: <20230601235909.0e1572c27e59112f9d0cbe86@sraoss.co.jp> <20230601034703.9e4f81f5d92ae6e3949b84d2@sraoss.co.jp> <20230628170604.505955118ac2f91abd554f13@sraoss.co.jp> In-Reply-To: From: jian he Date: Sun, 2 Jul 2023 08:25:12 +0800 Message-ID: Subject: Re: Incremental View Maintenance, take 2 To: Yugo NAGATA Cc: pgsql-hackers@postgresql.org Content-Type: multipart/alternative; boundary="00000000000000b37405ff761624" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000000b37405ff761624 Content-Type: text/plain; charset="UTF-8" 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" --00000000000000b37405ff761624 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

This is probably not trivial.=C2=A0
In = function=C2=A0 apply_new_delta_with_count.

=C2=A0appendStringInfo(&= ;querybuf,
"WITH updt AS (" /* update a tuple if this ex= ists in the view */
"UPDATE %s AS mv SET %s =3D mv.%s OPERATO= R(pg_catalog.+) diff.%s "
"%s " /* SET clauses= for aggregates */
"FROM %s AS diff "
"WHE= RE %s " /* tuple matching condition */
"RETURNING %s= " /* returning keys of updated tuples */
") INSERT INT= O %s (%s)" /* insert a new tuple if this doesn't existw */
= "SELECT %s FROM %s AS diff "
"WHERE NOT EXISTS (S= ELECT 1 FROM updt AS mv WHERE %s);",

---------------------
&q= uot;) INSERT INTO %s (%s)" /* insert a new tuple if this doesn't e= xistw */
"SELECT %s FROM %s AS diff "

the INSER= T INTO line, should have one white space in the end?=C2=A0
also "exis= tw" should be "exists"

--00000000000000b37405ff761624--