Feedback-ID: ia2694551:Fastmail
Date: Fri, 28 Jun 2024 13:27:17 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tender Wang <tndrwang@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, 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: <202406281127.piekjwgnulgf@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="q72ne4r752oxakpn"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAHewXNkyq88YBfCMbgnbquEwi+f5nyGOZ1axmo4ueLmkC=Am_A@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202406281127.piekjwgnulgf%40alvherre.pgsql>
Precedence: bulk


--q72ne4r752oxakpn
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On 2024-Jun-12, Tender Wang wrote:

> I think what you said above. I feel that we need that "tp_pkey" is a
> partition
> of the partitioned index "t_a_idx".  For example, below statement:
> 
> test=# alter table tp drop constraint tp_pkey;
> ERROR:  cannot drop index tp_pkey because index t_a_idx requires it
> HINT:  You can drop index t_a_idx instead.
> 
> If "tp_pkey" is not a partition of "t_a_idx", the primary key "tp_pkey" can
> be dropped.

I guess you could also fix this by adding a PK constraint to the parent
table, so that the situation is consistent in the other direction.

Here's a proposed patch for master only.  It turns all three situations
being reported into ereport(ERROR); in one case I have an XXX comment,
because we have an alternative when attaching a partition that already
has a PK to a partitioned table that has a non-PK index: just create a
separate index in the partition.  But that would cause slowness, which
is probably undesirable.  I'm inclined to just remove the XXX comment,
but if anyone has other thoughts, they are welcome.

I add an errhint() to one of these new errors, but I'm undecided about
that idea --- should we add errhint() to all three messages, or none?

We can't do this in back branches, of course (incl. 17 at this point),
because that might break existing applications, which means we need to
adjust behavior in some other way, probably similar to what Tender Wang
suggested, namely just don't crash on detach, or something like that.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

--q72ne4r752oxakpn
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment;
	filename="v2-0001-WIP-don-t-let-constraint-indexes-attach-to-raw-in.patch"