Feedback-ID: i0fe9450f:Fastmail
Date: Wed, 12 Jun 2024 15:12:31 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Tender Wang <tndrwang@gmail.com>
Cc: 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: <Zmk8T4KtTjLCKR5C@paquier.xyz>
References: <18500-62948b6fe5522f56@postgresql.org>
 <ZmawLsFY3JStl85w@paquier.xyz>
 <CAHewXNnZhGT1eM58-=5MQQ=moJ4hjqd3aDscxPSmB+SzwN3oUQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="lf+TnQYDYXbknopl"
Content-Disposition: inline
In-Reply-To: 
 <CAHewXNnZhGT1eM58-=5MQQ=moJ4hjqd3aDscxPSmB+SzwN3oUQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Zmk8T4KtTjLCKR5C%40paquier.xyz>
Precedence: bulk


--lf+TnQYDYXbknopl
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Jun 12, 2024 at 01:49:32PM +0800, Tender Wang wrote:
> Hi, all
>=20
> I found another crash case as below:
> CREATE TABLE t (a integer) PARTITION BY RANGE (a);
> CREATE TABLE tp (a integer PRIMARY KEY);
> CREATE UNIQUE INDEX t_a_idx ON t (a);
> ALTER TABLE t ATTACH PARTITION tp FOR VALUES FROM (0) TO (1000);
> ALTER TABLE t DETACH PARTITION tp;
>=20
> If index of parent is not created by adding constraint, it will trigger
> assert fail.

The 4th command is I think incorrect to attach "t_a_idx" to "tp"'s
primary key index because "t" does not have an equivalent entry in
pg_constraint.  So that does not address the actual issue.

> I look through the DetachPartitionFinalize(). The logic of detach indexes
> finds a pg_inherits tuple,
> then it goes to detach the constraint between parent and child. But in
> AttachPartitionEnsureIndexes(),
> it check whether constraint of parent is valid or not. If it is valid, th=
en
> the code will build constraint
> connection between parent and child.
>=20
> I think we can do this check in DetachPartitionFinalize(). We skip to
> detach the constraint if parent actually
> does not have one. I try to fix theses issue in attached patch.

Are you sure that this is correct?  This still makes "tp_pkey" a
partition of "t_a_idx" while the parent table "t" has no constraint
equivalent to "tp"'s primary key.  It seems to me that the correct
answer in your command sequence is to *not* make "tp_pkey" a partition
of the partitioned index "t_a_idx", and leave it alone.  Attaching
"tp_pkey" as a partition of "t_a_idx" is only OK as long as this index
is used in a constraint equivalent to "tp_pkey".

Having a PK on a partition while the parent does not have one is
covered by a regression test in indexing.sql (see "Constraint-related
indexes" and "primary key on child is okay").
--
Michael

--lf+TnQYDYXbknopl
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmZpPE8ACgkQnvQgOdby
QH04BBAAjyoECLocAYnvs8g5PvBvj8QkFZxoQuV1yjDeedS4Eo8fu0WwUhFNfhUG
FSqfTKLy46U4JJ4x0bYXoLymyG8E+gV852mQW//ZFp6FcTOTMge+ryPq+eTEFInT
Cacq1DLPA35dWizIxG8P24B7wlXXFus3GzM3PqEjQ/258Dvr00xRnem/clOwlnMi
1TlCxGbcBzanOAHOtj+gVjpLZyk85+0aIzbnhyqxhwnWkmEhHUaxNn80X6lGLyRf
mixnCw6ss3lNEnghW1AK4UuVPhIVRZ+/pUxDNUtdS4MTYPch7vGnFytHGeLlUxkw
kTRPHbX6wdu3EjVMCJ0ApivC4ED8vaGwmkLBwsWF3j3GPBb+mY+0XZpp2/Qq7H06
Dr3IAphptBvgWTLbscHQcsAz+r+IPEEi2XfYExq+bqHtlLvcsqvePKQZMjx4pcYx
ATNGfP8gE4xnUgpFW+u+OOyKIlD+042F6/Cox0a2CFVfcpShCPZDDYMI1SBotd+s
M7+nerkChOOHcE4llnT6v5eNxaXWdMR0MkQBHAbM/GKxi2yNNLg3HBWb8BjdvHqw
02m3+yFVso3dmLTXYJVbaFRzlBdVXBz7acXDj5t7474UTGLFajqc4iJPTrD+PCe8
/NdYxUQHMf2+7GtGmQH2IJ5lqVGJFUNCZeiwuYwqRg//6dxzorg=
=Sb8i
-----END PGP SIGNATURE-----

--lf+TnQYDYXbknopl--