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 1xSxLv-00000002ILM-12xX for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Nov 2026 14:06:35 +0000 Received: from localhost ([127.0.0.1]:52156 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xSxLt-00000001NGU-0CqM for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Nov 2026 14:06:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:48588) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSxIG-00000001KQ6-3f4l for pgsql-bugs@lists.postgresql.org; Mon, 16 Nov 2026 14:02:48 +0000 Received: from mail-yw1-x1136.google.com ([2607:f8b0:4864:20::1136]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xSxIF-00000001ELs-2M8g for pgsql-bugs@lists.postgresql.org; Mon, 16 Nov 2026 14:02:48 +0000 Received: by mail-yw1-x1136.google.com with SMTP id 00721157ae682-8b4d6981c67so6697707b3.1 for ; Mon, 16 Nov 2026 06:02:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1794837767; x=1795442567; 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=G+vY1dlFT182JV3SCSoze8+ueXtwcOchGQNn0zGbICQ=; b=ICLGVh71DlmkdupzQsXys5X9NUkAhUMC3LBCHnXFiSHHkzI/DleN/qO4d2IxmvUifR hXWlMF55XSLIORsJsgymCQEM9B2EiAEzt2504gcb6z9mqcu0Ok4epCzu1wBLq+AKKej6 a5LespHl6m8OKb3S60tPadvn+thRzmkuuO4CwEG/g1k3CqaiKpXvIvu2ULkSpwRZSfe9 ag4VA7UaX4FhGh4gp5L4kJkR7y3KFjIgCVLNx5WxxGrOggp3p3BkfiAGtzpHONLwMZks aXLoSMFr8qvmPc9XnoLqI3n7Amslk8JMy6HyPW4jRzgehlkFGyphv2QzDUPAShic12sB rrZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1794837767; x=1795442567; 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=G+vY1dlFT182JV3SCSoze8+ueXtwcOchGQNn0zGbICQ=; b=lB78OT6mLp3mkw1+SH3v7sS+F6PhZROS2vebMIKRub1+xDxL02CQFpdLiivwLLrf06 Y98oDGOXRIrDdzbASUgFabNtHSaeCMy2kiwozDcWFz6uDd+JGtAhmozeI3bSDnfPU8pn Zi+x0R756Iai4vyXkLiNlCiUFzE1HEbR8aCaDxm+SYG8TbtatFU76V9bWwn80fEjlZxG BLx/zpIejpFbG0WsSn87dVCXnmlZkWdtO8WlRtg60H5f/52kX12WoDTG9ssoWkRfRp5u U2IDBxJdYXbxcZlOfn1ppTxnu8zLo2JD9JGirYcWj1hwjm6RVB8bu/bsJOQabdSIrdZC Saww== X-Forwarded-Encrypted: i=1; AL+hErFnfdi2Pp16CPyJuAclr9ku8Y7sQnu7Wu6VuFkP5PXiTMFSf4W+LvpW7cAriZcDcfzivFhMtLlK3rsU@lists.postgresql.org X-Gm-Message-State: APuU4Z9cgLvLKr6heCGP9RGZiPxRewmZeZcyN2FbjKtBo/9kg8r5SNlo 8LRvNM2pY0NZl34ganyIeg4xRZ2JkypMC1shd1k5LxoEcFJUI/jCzAkc X-Gm-Gg: AZFdTZddJjnUOI4Kv64pIHzDPQ3aAumcQv9YXA1s/IAoHRhxznLfsHZ05VwaaJ3KK/3 Na1DFgVw/jYLF3nS+BFNyZ93lAtamt105ccHK7zEMskGJVa1bjRV9OJ2SLXAold9IOfmBTcWtWs bIp5pnBfLbSgRk3DL6XbisMvRFMsWCPG6omGr0G2LLjLQ1ieOazsbk2TFR+TwY/o/6tn90kHhmu dIFS43OiyQZJz903rDdfFCB/AwmqbLwD6pE2UGM1o8ehemzIb1v/sBJqZXdnQZMNk5x9Xl+xsHS esjuwsahpiAQmud3IdFAKNTAwqkTc7B8x34TqL0myUvxfm2w4k9zJuQ5lw/6tuT6V6O/F4/znBs npeyqXmn3f1PfcEPn/Xl7ftitr5LzCYDmX80rdDZiXrQ5qYg1JPuJeLXQNNO+w0lLTQPguXbvlO wdA7snp3MOjv0BzDwllclvGS0m09Van/ZXfyXeIKYCkbhXHqg52L3hafAw5IhQE2aHERy2gc+0q N4ZGyOExlL5/xQL2RIOUxrGI3p7H8BzNLql/CJmUZ8NqfFvmnDKpKRKaDxDGcKURSCOiCQrPZOu pjXq/oZh5uagQ8Qu0QmmI3Vegr5liRjEmbBPJwyPpF/pyfJz+bjHStNwOheJztjpts5537vu6EE zcAnAdIt5OWelMnyM5vHb5appDjksLb3XiGsABEMH/uSr7pGG2Hb9F8iTtYBA X-Received: by 2002:a05:690c:4990:b0:8b4:d9d2:df80 with SMTP id 00721157ae682-8b4d9d2e1d8mr11901037b3.24.1794837766387; Mon, 16 Nov 2026 06:02:46 -0800 (PST) Received: from NOTEBOOK-LAILA ([2804:14d:5c27:1387:a053:f15d:8c82:ed64]) by smtp.gmail.com with ESMTPSA id 00721157ae682-8b4aaa18121sm85116597b3.45.2026.11.16.06.02.43 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 16 Nov 2026 06:02:44 -0800 (PST) From: Joao Rito To: Dilip Kumar Cc: shihao zhong , Kirill Reshke , 303677365@qq.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Date: Mon, 16 Nov 2026 11:02:39 -0300 Message-ID: <20261116140239.1145-1-joaovictor.rito@gmail.com> X-Mailer: git-send-email 2.53.0.windows.1 In-Reply-To: References: <19817-4e72209979d7bdd5@postgresql.org> 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 Dilip, Thank you for the patch. I tested v3-0001 on current master. It fixes the report for me. These are my results. Setup: master at 7fc2f0423320 (2026-11-16), Debian, gcc 14, configure with --enable-cassert --enable-debug --enable-tap-tests --without-icu. I ran the cases on plain master, then applied v3-0001, rebuilt, and ran them again. Reproduction from the report (b2_root / b2_p1): - Without the patch, CREATE UNIQUE INDEX b2_root_ui returns CREATE INDEX. b2_p1_bad is attached to b2_root_ui with indisvalid = f and indisready = f, and b2_root_ui has indisvalid = f. The insert of (1,'x'),(1,'x') succeeds (3 rows with a = 1), and ON CONFLICT (a) fails with "there is no unique or exclusion constraint matching the ON CONFLICT specification". - With the patch, a new leaf index b2_p1_a_idx is built, it is attached, and b2_root_ui is valid. The duplicate insert fails with "duplicate key value violates unique constraint "b2_p1_a_idx"". ON CONFLICT (a) DO NOTHING works. The old b2_p1_bad stays behind as an invalid, unattached index, and it can be dropped. Other cases, all with the patch. Shihao already confirmed the PK case on v3; I list it for completeness: - A three-level tree (root, a partitioned mid level, two leaves under it, plus one leaf directly under root) with a leftover invalid index on one lower leaf. Both partitioned indexes are valid, and the leftover is not attached. The unique index rejects a duplicate on the two leaves I tried (the one with the leftover and its sibling). - ALTER TABLE ONLY s ADD PRIMARY KEY (a) on a partitioned sub-table, then ALTER TABLE r ADD PRIMARY KEY (a), and then ADD PRIMARY KEY on the leaf. No error. This is the case Shihao raised on v2 and confirmed on v3. The output is the same as on master. - ALTER TABLE ... ATTACH PARTITION with a leftover invalid index on the table being attached: master and patched give the same result (a new valid index is attached, the leftover stays out). - CREATE UNIQUE INDEX ... ON ONLY, CIC on each leaf, then ALTER INDEX ... ATTACH PARTITION: the parent becomes valid. CIC directly on the partitioned table still gives the same error as before. - A failed non-unique CIC (expression 10/a with a row a = 0), then CREATE INDEX on the parent: the invalid leaf index is skipped as well, and a new valid one is attached. - A leftover named s8_p1_a_idx (failed unique CIC), then CREATE UNIQUE INDEX on the parent: the new child is named s8_p1_a_idx1, so there is no name collision. - After removing the duplicate with the leftover still in place, REINDEX INDEX CONCURRENTLY on the leftover makes it valid, and a later CREATE UNIQUE INDEX on the parent attaches it. If the duplicate is still there, CREATE UNIQUE INDEX on the parent fails on the leaf and leaves nothing behind. - pg_dump of the patched result restores with no errors. The restored database has 2 invalid indexes, and the original has 7 (5 leftovers and 2 ON ONLY parents). I did not list the 2; I expect they are the ON ONLY parents from the primary key case. The duplicate insert fails in the restored database. - amcheck bt_index_check(..., heapallindexed) passes on the 13 valid btree indexes from these cases. Test suites with the patch: - make check: all 243 tests passed. - make -C src/test/isolation check: all 139 tests passed. - contrib/amcheck check: all 4 tests passed. - src/bin/pg_dump t/002_pg_dump.pl: 13361 tests, all passed. The patch applies with git apply and "git diff --check" is clean. The build has no warnings. On the change: skipping only invalid indexes that are not partitioned indexes looks right to me. It keeps the ON ONLY partitioned indexes attachable, and it matches the ATTACH PARTITION path, which already skips invalid indexes (e90e9275f56). The new test expects errors in the right places (the CIC on duplicate keys, the parent CREATE UNIQUE INDEX and the duplicate insert). It uses a duplicate key, not the expression index from v1 that Kirill found to be flaky. I ran make check only once, so I cannot say more about flakiness. Two small points, both optional: - The leftover invalid index stays in place, and the command does not say so. A NOTICE or a doc sentence that says to drop the leftover would help users who see two indexes on the same column. The new doc sentence, "Invalid indexes on leaf partitions are skipped.", could say "are skipped, not attached and not dropped". - The commit message says that the broken child "could no longer be dropped directly" once attached. I did not test that on the unpatched tree, so I take it from the message. What I did not test: - Other branches (the report says 18.6 is affected too), so I cannot say how the patch looks as a backpatch. - Concurrent DDL or DML while CREATE INDEX recurses over the partitions. - Hash and list partitioned tables, non-btree access methods, partial indexes, and indexes with INCLUDE columns. - Exclusion constraints on partitioned tables. - Tables with a large number of partitions, and performance. - Standby or logical replication behaviour. - Windows and 32-bit builds. - A run of make check on plain master without the patch. - Builds with ICU (I used --without-icu). Thanks, Joao Rito