Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSLS8-00000001li6-1LPt for pgsql-bugs@arkaria.postgresql.org; Sat, 14 Nov 2026 21:38:28 +0000 Received: from localhost ([127.0.0.1]:45032 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xSLS6-0000000EB7x-1QWR for pgsql-bugs@arkaria.postgresql.org; Sat, 14 Nov 2026 21:38:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:46038) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSLNw-0000000E67w-1dvT for pgsql-bugs@lists.postgresql.org; Sat, 14 Nov 2026 21:34:08 +0000 Received: from mail-vs1-xe2d.google.com ([2607:f8b0:4864:20::e2d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xSLNu-000000014Y2-1VHP for pgsql-bugs@lists.postgresql.org; Sat, 14 Nov 2026 21:34:08 +0000 Received: by mail-vs1-xe2d.google.com with SMTP id ada2fe7eead31-7d2bf046ceaso2534553137.3 for ; Sat, 14 Nov 2026 13:34:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1794692045; x=1795296845; darn=lists.postgresql.org; h=content-transfer-encoding:mime-version:references:in-reply-to :message-id:date:subject:cc:to:from:from:to:cc:subject:date :message-id:reply-to:content-type; bh=0aUsk0gYT3lU9d6NBJopJpvvU/3xpwm/0BzGiAT3pbo=; b=dVIuRTUqiZYk43GS8gKKjRoqcAq3BOuYOPWyi0XdxNbIjJcIHuQXWzFxlz6osbuuek V4SidVCifA1UmSm8dfg5mqBWX52gk4Px0xmRqFsFIqxTzzXch8EATGUjl/1EgU5tvtMe VllX39FB/iGdeOLWO+4Ee6d3aEBtIfA0R6QaP55gl7Q16Gii1uGF3qLHjz3WVW+u6e9C xhhNYDpsEgTp5KMCwiA1LFHortEmcuuFfmwNFw9wvsDMOZvhg00OCn3D+KCDgLxrLWvr KYeuGPR5QcaUsZS01TTjw7cYZRiwKaMN10xrssbAVKKFRooMa8hc01U64KtnQ9jhtPsw N4Hw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1794692045; x=1795296845; h=content-transfer-encoding:mime-version:references:in-reply-to :message-id:date:subject:cc:to:from:x-gm-gg:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to:content-type; bh=0aUsk0gYT3lU9d6NBJopJpvvU/3xpwm/0BzGiAT3pbo=; b=d7IiNuN2y1y806FxIjqS20vjvjTdsmqniFII70tTVef4EVMRv0Wh9tO5wf8KqZda1Y KRmPvAkVYCGvwMAUv6jPyjHBxezIXO+vE7+0zlwfgaHY7pNutkGs/556mJzPQrhvgQ07 2feCKDNAiZIg0gPSEKV2xKNoohHiJKPfQ8nv0IoAnuPS97PlKnnYq++Qd7JjFEfFQXeK SpKe8VKFuDPSH4VrwIeUVy5N23W/tJmJH9t1M/Uk2UPMJpOBTRz8bwdGbxS8axYq1nIB yEzkpj17d2asQdNybbnI0iVVdohYK9/SymlQ0t2UUegfaN9YkQubj5dz8l/NRV3ZpTOc MpLw== X-Forwarded-Encrypted: i=1; AL+hErGNgCpdmfQul5KiR0b8H3fV6Gi+arieRwkwwc9M/B23z+2iGdfaKlZxZvsjhmuww702EwbxjsY+1aru@lists.postgresql.org X-Gm-Message-State: APuU4Z9CGjM90EepYR6TsUj2zG9VSUel4J4v66hkGJjLyputb3vX/KCa 0SHIQ/gZYdNYP21NNOcvLDADUGoAcFRG5Qd66A8hcicqx0akM3HkvZ7f X-Gm-Gg: AYBFou2djS4UqoM7FekHQ4u7+HoaXobAMk37sItsJTE/u3y5kpLvN/ahJhnR3jHp2dz HH4/1sASjNjfBGG8+Ho6phGviOxENRvaYEj0RY81ilntZjTuM0PbPvdpSa8Pk0it5GCtqAXvW9g ZJThE1GVAOZ6E2CCfUp5wUmZrwbvJSdaBcYtsm64tAFf+HsdsnRLaa50INW6NkJGU2gb203bnuQ ZKgDJM+eph1kDW0zyXquGFRpIXUp6LKiTW1QDIp1HMjYcaMsWI1q0aNbSCR4MP49cmVN+dYUdOo BwseS1N5cQm5TM2rXxuSSXV3xIaOT7yIXDPWRPV+lSO6O1g3Mbyp0yvc0nEebB7ANMgEk8q1gPX bItjX69d1u+EWfYE4UVEMgJncCqjgWM6RtX0sj9+c7h0V1p5hyKmCp6J0ZZrCUx8k6gBey2Gu2Q 7hcaeTRrzot17gVjCqf4r7YuRgRqrJtp+O8uBddyqt77IVSXTo3lYQNXaO1ygUx4ZZS1p/mqqkG EHGN054KgqK88UnqGSOuijW8AU7NHlEG6lyxjSNuVnkssVVPjXvJBhB4MPNxTxuzALyxoxecWzs s8dyjRG9Kl+NpHsShO4Hm+I37n3cbIZv1Gts5mcsiNkDIVteCun+PQHb6b5AcYBKvVXrcR4diR3 3Jquym3KU9op/XRc4+IAHDPmqZDDY/QGQnVlQSfxsjZrzdR0N1myPL9zfwpQ5 X-Received: by 2002:a05:6102:94f:b0:7a5:391c:37ac with SMTP id ada2fe7eead31-7d64d6cb3e6mr2714864137.10.1794692044663; Sat, 14 Nov 2026 13:34:04 -0800 (PST) Received: from NOTEBOOK-LAILA ([2804:14d:5c27:1387:a053:f15d:8c82:ed64]) by smtp.gmail.com with ESMTPSA id ada2fe7eead31-7d64be7e2e9sm4117388137.0.2026.11.14.13.34.02 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sat, 14 Nov 2026 13:34:04 -0800 (PST) From: Joao Rito To: Rafia Sabih , surya poondla Cc: Yugo Nagata , Robert Haas , cca5507 , Giuliano Gagliardi , pgsql-bugs@lists.postgresql.org Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY Date: Sat, 14 Nov 2026 18:33:56 -0300 Message-ID: <20261114213356.310-1-joaovictor.rito@gmail.com> X-Mailer: git-send-email 2.53.0.windows.1 In-Reply-To: <20260731164448.f84babf2a951ad20e68058a8@sraoss.co.jp> References: <20260731164448.f84babf2a951ad20e68058a8@sraoss.co.jp> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Rafia, hi Surya, Thank you for the patches. I tested the v7 series (v7-0001 for the duplicate detection and v7-0002 for the nullable indexed columns). My results are good for 0001 and mixed for 0002, details below. What I did: - Applied both patches to current master (0dbe240dc0). They apply cleanly and build without warnings (-O2, --without-icu, no --enable-cassert). - make check passes (243 tests) on master, with 0001 and with 0001+0002, including the new matview regression tests. - Correctness, comparing master, 0001 and 0001+0002 on a few small cases: - Two identical rows (test, NULL) with a unique index on the first column: on master REFRESH ... CONCURRENTLY succeeds without error and leaves 1 row in the view while the query returns 2. With 0001 (and with 0002) it raises "new data for materialized view "m" contains duplicate rows"; the plain REFRESH also fails on this data (with a unique index error), so the results now agree. - Same with a third row added, (k1, NULL, 1) twice plus (k2, z, 2): master silently keeps 2 rows, 0001 raises the error. - Two (NULL, NULL) rows with a unique index on a: succeeds in all three builds, 2 rows. This also matches Yugo's note that (NULL, NULL) twice already worked on master. Two-column unique index with duplicates only through NULL: succeeds in all three. A true duplicate (r,s,2) twice errors in all three, only the message text changes (the "without any null columns" part is gone). NULLS NOT DISTINCT gives the same unique violation in all three. - 200k rows with about 20 percent NULLs, two unique indexes, then updates and deletes and a refresh: 0 rows missing and 0 extra in all three builds (198012 rows). - Performance on 2M rows (k is 50 percent NULL, unique indexes on (id) and on (k,id)) and on 1M rows (y all NULL, unique indexes on x and on y): - 0001 changes the plan of the duplicate pre-check from a merge semi join with two sorts (37.1 s) to a hash semi join (9.95 s), in one auto_explain run. - With 0002 I could not see the benefit. A refresh with no data change still deletes and inserts 3,000,000 tuples after 3 refreshes in all three builds, in both scenarios. The join of the diff query still has "mv.k = newdata.k" in the hash condition with 0002 (same plan as with 0001 alone). - I think this agrees with Surya's note of 21 July that 0002 does not help. My guess for the reason: 0002 skips nullable columns only when attr->attnotnull is set for some index column, but the columns of a materialized view show attnotnull = f even when the source column is NOT NULL (I checked pg_attribute). If I read the patch right, the new branch is then never taken for a materialized view. I did not debug this further, so please take it as a guess. The new regression tests for 0002 pass, but they only check that the refresh succeeds, so they would not notice this. Timings are noisy on my machine (one refresh of the 2M row case took from 24 s to 88 s), so I would not read anything into the elapsed times, only into the tuple counts and the plans. I did not test: --enable-cassert, TAP tests or check-world (only make check), meson builds, the back branches, concurrent refreshes, expression or partial unique indexes, row security, or materialized views bigger than 2M rows. Each timing is a single sequence of three runs. Thanks again, Joao Rito