Feedback-ID: i0fe9450f:Fastmail
Date: Mon, 10 Jun 2024 16:50:06 +0900
From: Michael Paquier <michael@paquier.xyz>
To: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18500: Detaching a partition with an index manually
 attached to the parent's index triggers Assert
Message-ID: <ZmawLsFY3JStl85w@paquier.xyz>
References: <18500-62948b6fe5522f56@postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="53o55ovLV7trgnvZ"
Content-Disposition: inline
In-Reply-To: <18500-62948b6fe5522f56@postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/ZmawLsFY3JStl85w%40paquier.xyz>
Precedence: bulk


--53o55ovLV7trgnvZ
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Sun, Jun 09, 2024 at 06:00:00AM +0000, PG Bug reporting form wrote:
> CREATE TABLE t (a integer) PARTITION BY RANGE (a);
> CREATE TABLE tp (a integer PRIMARY KEY);
> ALTER TABLE t ATTACH PARTITION tp FOR VALUES FROM (0) TO (1000);
>=20
> CREATE UNIQUE INDEX t_a_idx ON t (a);
> ALTER INDEX t_a_idx ATTACH PARTITION tp_pkey;
>=20
> \d+ t*
> ALTER TABLE t DETACH PARTITION tp;
>=20
> TRAP: failed Assert("constrForm->coninhcount =3D=3D 0"), File:
> "pg_constraint.c", Line: 873, PID: 3272276
> ...
> #5  0x000055b26dc9b76f in ExceptionalCondition (
>     conditionName=3DconditionName@entry=3D0x55b26dd2d254
> "constrForm->coninhcount =3D=3D 0",=20
>     fileName=3DfileName@entry=3D0x55b26dd2d230 "pg_constraint.c",
> lineNumber=3DlineNumber@entry=3D873) at assert.c:66

This DDL sequence completely breaks partitioned tree indexes AFAIU.
As reported, the inheritance count is wrong.  Anyway, from what I can
see, I think that the ATTACH PARTITION is wrong and it should issue an
error because this is attempting to attach an index used by a
constraint to a partitioned index which is *not* related to a
constraint in the parent.  I am pretty sure that more chirurgy around
AttachPartitionEnsureIndexes() with lookups at pg_constraint are
required here.

It's a bit disappointing that this would not work to make sure that
the index used by the partitioned table is on a primary key, ensuring
a consistent partition tree for the whole, with all indexes used in
the pk:
=3D# alter table t add primary key using index t_a_idx ;
ERROR:  0A000: ALTER TABLE / ADD CONSTRAINT USING INDEX is not
supported on partitioned tables
LOCATION:  ATExecAddIndexConstraint, tablecmds.c:9259=20

This sequence, however, is fine:
CREATE TABLE t (a integer) PARTITION BY RANGE (a);
CREATE TABLE tp (a integer PRIMARY KEY);
ALTER TABLE t ATTACH PARTITION tp FOR VALUES FROM (0) TO (1000);
alter table t add primary key (a);
ALTER TABLE t DETACH PARTITION tp;

Note that before the DETACH we have this index tree, with a consistent
coninhcount:
=3D# select * from pg_partition_tree('t_pkey');
  relid  | parentrelid | isleaf | level
---------+-------------+--------+-------
 t_pkey  | null        | f      |     0
 tp_pkey | t_pkey      | t      |     1
(2 rows)
--
Michael

--53o55ovLV7trgnvZ
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmZmsC4ACgkQnvQgOdby
QH3r9Q/+LLSLP4nfvtUiJdtt7O6K+oZHL9eg0cBWpvHxv6P/z7PUaVxL0FzvkWba
22ldPPWrzVRXv9CY3xGh21iEmk8b0uFcwuO1LpKTyZUbxR9ecWjTXNoTK90dpXqw
F3VD4ukwzDHSUeQ3AFaI4NGl3HdO0S6se00ngQJh6RKCAtkaccHfzG+Bmcd5p1BC
jkUZYt4SSBqsWgrQnkTlfoDjnAQtV0vQ1ZHTC0cKoCjzYK6O2F0mUeV2TJj38Uo5
qMrK6Uxg5emrEcJF6WkzeI730jNujplgKUtxB1QzFWkLBoczAGfjmTB8vGbe7tL4
0zdSLvVB6fUYyft/zj2HtJFfAOsx8UQidkmKrAZpFzYHxpJt9ezkTxaZMLIzwnLv
kbsYa50XkDrDdxVOPYg1sv1+qxaYRGTW8wxhHhdbZk1tiQUMwOfPTCPSzh09O5VI
8OxxtEXv8WS4zo/hheKeW7LAUEk8mqw47z/nakfG+XzoOC8E1sTF/srRDI65LXZL
GQegwBu+2plfbYvVpfukIaBWPoZ2PeNv0sVzhlT3KCQSXrBD12sYtpNgLDOeE1bo
g38ug7fXYCHAoM9J45l8cLmbPqGJQIztku4TFx4ergVTxhd6iEMjxnB23TiLEgzd
aJketebwiMFGRMys1rDYq/sq7EQggN3la68LY4Lviak2k/Tksgw=
=zoad
-----END PGP SIGNATURE-----

--53o55ovLV7trgnvZ--