Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

Tender Wang <tndrwang@gmail.com> 于2024年8月22日周四 11:19写道：

>
>
> Alvaro Herrera <alvherre@alvh.no-ip.org> 于2024年8月22日周四 06:00写道：
>
>> On 2024-Aug-19, Alvaro Herrera wrote:
>>
>> > I haven't pushed it yet, mostly because of being unsure about not doing
>> > anything for the oldest branches (14 and back).
>>
>> Last night, after much mulling on this, it occurred to me that one easy
>> way out of this problem for the old branches, without having to write
>> more code, is to simply remove the constraint from the partition when
>> it's detached (but only if they reference a partitioned relation).  It's
>> not a great solution, but at least we're no longer leaving bogus catalog
>> entries around.  That would be like the attached patch, which was cut
>> from 14 and applies cleanly to 12 and 13.  I'd throw in a couple of
>> tests and call it a day.
>>
>
> I apply the v14 patch on branch REL_14_STABLE. I run this thread issue and
> I
> find below error.
> postgres=# CREATE TABLE p ( id bigint PRIMARY KEY ) PARTITION BY list (id);
> CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
> CREATE TABLE r_1 (
>     id   bigint PRIMARY KEY,
>     p_id bigint NOT NULL,
>     FOREIGN KEY (p_id) REFERENCES p (id)
> );
> CREATE TABLE r (
>     id   bigint PRIMARY KEY,
>     p_id bigint NOT NULL,
>     FOREIGN KEY (p_id) REFERENCES p (id)
> ) PARTITION BY list (id);
> ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1);
> ALTER TABLE r DETACH PARTITION r_1;
> CREATE TABLE
> CREATE TABLE
> CREATE TABLE
> CREATE TABLE
> ALTER TABLE
> ERROR:  cache lookup failed for constraint 16400
>

I guess it is because cascade dropping, the oid=16400 has been deleted.
Adding a list that remember dropped constraint, if we find the parent of
constraint
is in the list, we skip.

By the way, I run above SQL sequences on REL_14_STABLE without your partch.
I didn't find reporting error, and running oidjoins.sql  didn't report
warnings.
Do I miss something?


-- 
Tender Wang