Feedback-ID: ia2694551:Fastmail
Date: Thu, 27 Jun 2024 20:41:47 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Michael Paquier <michael@paquier.xyz>
Cc: Tender Wang <tndrwang@gmail.com>, 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: <202406271841.t2bfpwp7tahf@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <Zmk8T4KtTjLCKR5C@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/202406271841.t2bfpwp7tahf%40alvherre.pgsql>
Precedence: bulk

On 2024-Jun-12, Michael Paquier wrote:

> On Wed, Jun 12, 2024 at 01:49:32PM +0800, Tender Wang wrote:

> > 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;
> > 
> > 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.

Actually, the partition index is attached to the parent index already at
CREATE UNIQUE INDEX, so it's the third line that's the problem (but what
you say is true too if the user says CREATE UNIQUE INDEX ON ONLY
parent).

I tend to agree that a good, consistent fix for this would be to forbid
a PK in a partition from becoming a child of a non-constraint index in
the parent ... A few ereport(ERROR)s sprinkled in various places might
be sufficient in the master branch, but perhaps we shouldn't disallow it
in stable branches because that might be problematic for existing apps;
and I suspect that we'd need to install protections in pg_upgrade to fix
databases from earlier branches being upgraded with the given hierarchy
(or just prevent the upgrade in --check and tell the users to fix it.)

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/