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-000000029Tu-1NJs 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-2Uhm 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]:42168) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgRD-00000008ETF-2q48 for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:50:07 +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 1xMgRB-00000001iFo-2svY for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:50:07 +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=018GJ080kl5wyzyNUvC4ZF+fp5vNkUMG8WGm70lhhac=; b=4LLDt+VNjiypgyVBVq8XrifW5O ujPkoCB8I8noRMwLrO6nKu5LhT/o9P8PzDg/5q8QqlzVzMy8ges1lU2BtZ+gdTDc8hHLoCu9uK9hZ MaCnGU9Uvnc+XBxo/L82uLmtw2PSUODQ1olkj/xJ8gOopgLg0cUW44ASDC6XetjWL3hZ0Wbp8sQkW LZqq9s16QlklDHRFwJ52YpOJmmKpFTfssdpMA/DumcruqCDHo8bGYTleXfReXwD61RNrcOO4to0zt beMlz9QxbajhC0njD4BkHuLIq11qv7R2tkLyJBKMAodA70qK/AqWcgDXH27Ma0r3yc7ffox7hQOiF RTxr59TQ==; 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 1xMgRA-00000004luA-1e8C for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:50:04 +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 1xMgR8-000000076Wo-1ZL5 for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:50:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19818: ATTACH PARTITION / CREATE INDEX accept a child index whose deferrable property differs from the pare 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:49:16 +0000 Message-ID: <19818-3f0f800aae5c2c9d@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: 19818 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux (TencentOS 4.4, x86_64) Description: =20 Hello, CompareIndexInfo() (src/backend/catalog/index.c:2570) decides whether an existing partition index can be attached under a partitioned parent index. It compares uniqueness, NULLS NOT DISTINCT, expressions, predicates and collations -- but it does not compare indimmediate (the deferrable property). As a result, ATTACH PARTITION and CREATE INDEX silently attach a child index whose deferrable property differs from the parent index, and every INSERT ... ON CONFLICT on that partition is then permanently broken. The most natural way to hit this is to attach a partition that already has a deferrable primary key: CREATE TABLE z9_root (a int, b text, PRIMARY KEY (a)) PARTITION BY RANGE (a); CREATE TABLE z9_p1 (a int, b text, PRIMARY KEY (a) DEFERRABLE); ALTER TABLE z9_root ATTACH PARTITION z9_p1 FOR VALUES FROM (0) TO (1000); -- succeeds silently: z9_p1_pkey (indimmediate=3Df) attached under -- z9_root_pkey (indimmediate=3Dt) -- the consequence: ON CONFLICT is now unusable on this partition INSERT INTO z9_p1 VALUES (1,'x') ON CONFLICT (a) DO UPDATE SET b =3D 'y= '; -- ERROR: ON CONFLICT does not support deferrable unique constraints/ -- exclusion constraints as arbiters Verification of the resulting state: SELECT c.relname, i.indisunique, i.indimmediate, i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid =3D i.indexrelid WHERE c.relname IN ('z9_p1_pkey','z9_root_pkey'); -- z9_p1_pkey | t | f | t -- z9_root_pkey | t | t | t Expected: ATTACH PARTITION / CREATE INDEX should reject (or at least warn about) a child index whose indimmediate differs from the parent, the same way it already handles the other index properties it compares. The consumer side already treats this as an error: ExecCheckIndexConstraints() (src/backend/executor/execIndexing.c:607) refuses !indimmediate arbiters, which is what makes every later ON CONFLICT fail. Actual: the attach succeeds with no warning of any kind, and every subsequent INSERT ... ON CONFLICT on that partition fails with "ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters", with no hint that the ATTACH was the cause. Related: this is the sibling path of the issue recently discussed by Zsolt Parragi ("Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master", 2026-08-28), where commit 90eae926abbb had taught IsIndexCompatibleAsArbiter() to match leaf indexes, and commit dc5f8cb9346 then made IsIndexCompatibleAsArbiter() compare indimmediate for the routed-insert path. The path reported here (attaching a child index to a parent through CompareIndexInfo()) was not covered by that fix. Reproduced on: - PostgreSQL 20devel (snapshot of 2026-10-27, x86_64-pc-linux-gnu) - PostgreSQL 18.6 (REL_18_STABLE, self-built) A draft fix location would be CompareIndexInfo() (src/backend/catalog/index.c): include indimmediate in the comparison, mirroring how the other index properties are already compared there. Regards, Chunling Qin