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 1xMgsi-000000029Tv-0pgQ for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:32 +0000 Received: from localhost ([127.0.0.1]:51196 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMgsf-00000008qJy-2SQd for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:47000) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgIa-00000008CGD-1csg for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:41:12 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgIV-00000001iC3-3PBG for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:41:12 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=PWsWFEwwbSk5c/IxmKKIBYa4I6GaKmOl7MXTB++VWoA=; b=wkz4uka0/+EAhNr+JLDLvm8PeB 0Q7pAGgG5PFqKKfhKA/B8AJZsvQu02JXYJgFLJ51dpbcx2XXbOgnDLvbGLL/poNqWNOmJ2GoovqyB kpagxAt2DGX64NBGvIfVHoqvswZfZHNXusWzL+o6m81pAZfYwr4e6mU83Ju8q1oz6ky3kyS2F/23+ kG70X3RAQj+2s+ZQwMj0FT7HxUzsAzVGQfJY5hjYG355pH0IcOL4nPY2j31KZuKfBYF+QxeF145qZ kPnMclUYgKOpKGn60sfr3yrmArJwLKCUE+cBl2/zx4hL24Pum4wwV6fRw3StB8fkJdhiRqJ8ERGie k/UFgTAg==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgIT-00000004lhf-0u1G for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:41:06 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMgIQ-0000000769f-2Gjy for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:41:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Fri, 30 Oct 2026 06:40:13 +0000 Message-ID: <19817-4e72209979d7bdd5@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19817 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux (TencentOS 4.4, x86_64) Description: =20 Hello, When CREATE UNIQUE INDEX is run on a partitioned table, DefineIndex() recurses to each partition and reuses an already-existing index it considers "equivalent" (src/backend/commands/indexcmds.c, the recursion loop around lines 1450-1495). That equivalence check only compares the index definition; it does not check indisvalid or indisready. As a result, an index left invalid (and not ready) by a previously failed CREATE INDEX CONCURRENTLY is silently attached as a child index, the parent index is created with indisvalid=3Dfalse, and the whole chain reports success -- while in fact the UNIQUE constraint is not enforced at all. Steps to reproduce (self-contained, no data needed): -- a partition with a leftover invalid index from a failed CIC CREATE TABLE b2_root (a int, b text) PARTITION BY RANGE (a); CREATE TABLE b2_p1 PARTITION OF b2_root FOR VALUES FROM (0) TO (1000); INSERT INTO b2_p1 VALUES (1,'x'), (1,'dup'); -- duplicate key CREATE UNIQUE INDEX CONCURRENTLY b2_p1_bad ON b2_p1 (a); -- fails, leaves -- b2_p1_bad behind with indisvalid=3Df, indisready=3Df DELETE FROM b2_p1 WHERE b =3D 'dup'; -- table is clean now -- the silent failure: CREATE UNIQUE INDEX b2_root_ui ON b2_root (a); -- returns -- CREATE INDEX, no warning -- state after: the invalid leftover is attached as a child index SELECT c.relname, i.indisvalid, i.indisready FROM pg_index i JOIN pg_class c ON c.oid =3D i.indexrelid WHERE c.relname IN ('b2_p1_bad','b2_root_ui'); -- b2_p1_bad | f | f (parent: b2_root_ui) -- b2_root_ui | f | t -- consequence a: uniqueness is not enforced, duplicates are accepted INSERT INTO b2_p1 VALUES (1,'x'), (1,'x'); -- succeeds -- consequence b: ON CONFLICT can no longer be used on this table INSERT INTO b2_p1 VALUES (1,'x') ON CONFLICT (a) DO NOTHING; -- ERROR: there is no unique or exclusion constraint matching -- the ON CONFLICT specification Expected: the recursion loop should skip (or hard-fail on) child indexes that are not indisvalid/indisready, at minimum emit a WARNING. For comparison, the ATTACH path already does a full check: AttachPartitionEnsureIndexes() (src/backend/commands/tablecmds.c) refuses in that situation, and the project has recently applied the same standard to REPACK ("Fail REPACK in presence of invalid indexes", commits 0c5d6269614 and 78142821dfd). Actual: CREATE UNIQUE INDEX reports success and silently attaches the invalid leftover index; the unique constraint enforces nothing; no warning of any kind is emitted. Reproduced on: - PostgreSQL 20devel (snapshot of 2026-10-27, x86_64-pc-linux-gnu) - PostgreSQL 18.6 (REL_18_STABLE, self-built) The behavior looks the same on both branches, so this appears to be long-standing rather than a recent regression. A draft fix location would be the recursion loop in DefineIndex() (src/backend/commands/indexcmds.c:1450-1495): before reusing an existing partition index, require indisvalid && indisready, mirroring AttachPartitionEnsureIndexes(). Regards, Chunling Qin