From: | Tender Wang <tndrwang(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | Junwang Zhao <zhjwpku(at)gmail(dot)com>, Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Alexander Lakhin <exclusion(at)gmail(dot)com>, Baehler Thomas SBB CFF FFS <thomas(dot)baehler2(at)sbb(dot)ch>, Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
Subject: | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |
Date: | 2024-08-22 07:07:26 |
Message-ID: | CAHewXNnJh4aFVs6+3G6LEr=+OpE0=1ZWEq7wd30FyGTXv6F+hg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2024-08-22 07:10:52 | Re: Disallow USING clause when altering type of generated column |
Previous Message | Michael Paquier | 2024-08-22 06:24:26 | Re: Instability with incremental backup tests (pg_combinebackup, 003_timeline.pl) |