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

Tender Wang <tndrwang(at)gmail(dot)com> 于2024年8月22日周四 11:19写道：

>
>
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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