From: | tender wang <tndrwang(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |
Date: | 2023-08-03 06:55:03 |
Message-ID: | CAHewXNkL1QC_erA_7mkFHHAU9c56fa3oxuSvRt9cmtyZRY8Zhw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I think old "sub-FK" should not be dropped, that will be violates foreign
key constraint. For example :
postgres=# insert into r values(1,1);
INSERT 0 1
postgres=# ALTER TABLE r DETACH PARTITION r_1;
ALTER TABLE
postgres=# delete from p_1 where id = 1;
DELETE 1
postgres=# select * from r_1;
id | p_id
----+------
1 | 1
(1 row)
If I run above SQLs on pg12.12, it will report error below:
postgres=# delete from p_1 where id = 1;
ERROR: update or delete on table "p_1" violates foreign key constraint
"r_1_p_id_fkey1" on table "r_1"
DETAIL: Key (id)=(1) is still referenced from table "r_1".
Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 于2023年7月31日周一 20:58写道:
> On 2023-Jul-05, Jehan-Guillaume de Rorthais wrote:
>
> > ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1);
> >
> > The old sub-FKs (below 18289) created in this table to enforce the action
> > triggers on referenced partitions are not deleted when the table becomes
> a
> > partition. Because of this, we have additional and useless triggers on
> the
> > referenced partitions and we can not DETACH this partition on the
> referencing
> > side anymore:
>
> Oh, hm, interesting. Thanks for the report and patch. I found a couple
> of minor issues with it (most serious one: nkeys should be 3, not 2;
> also sysscan should use conrelid index), but I'll try and complete it so
> that it's ready for 2023-08-10's releases.
>
> Regards
>
> --
> Álvaro Herrera 48°01'N 7°57'E —
> https://www.EnterpriseDB.com/
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2023-08-03 07:08:13 | Re: Improve const use in zlib-using code |
Previous Message | Jeff Davis | 2023-08-03 06:50:28 | Re: Faster "SET search_path" |