Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
| От | tender wang |
|---|---|
| Тема | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |
| Дата | |
| Msg-id | CAHewXNkM8y+T7KsrQLT2mR0y3i1p6J9dwN-s0uGFCjgjkx+URA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails (tender wang <tndrwang@gmail.com>) |
| Ответы |
Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |
| Список | pgsql-hackers |
Oversight the DetachPartitionFinalize(), I found another bug below:
postgres=# CREATE TABLE p ( id bigint PRIMARY KEY ) PARTITION BY list (id);
CREATE TABLE
postgres=# CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE r_1 (
postgres(# id bigint PRIMARY KEY,
postgres(# p_id bigint NOT NULL
postgres(# );
CREATE TABLE
postgres=# CREATE TABLE r (
postgres(# id bigint PRIMARY KEY,
postgres(# p_id bigint NOT NULL,
postgres(# FOREIGN KEY (p_id) REFERENCES p (id)
postgres(# ) PARTITION BY list (id);
CREATE TABLE
postgres=# ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1);
ALTER TABLE
postgres=# ALTER TABLE r DETACH PARTITION r_1;
ALTER TABLE
postgres=# insert into r_1 values(1,1);
ERROR: insert or update on table "r_1" violates foreign key constraint "r_p_id_fkey"
DETAIL: Key (p_id)=(1) is not present in table "p".
CREATE TABLE
postgres=# CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE r_1 (
postgres(# id bigint PRIMARY KEY,
postgres(# p_id bigint NOT NULL
postgres(# );
CREATE TABLE
postgres=# CREATE TABLE r (
postgres(# id bigint PRIMARY KEY,
postgres(# p_id bigint NOT NULL,
postgres(# FOREIGN KEY (p_id) REFERENCES p (id)
postgres(# ) PARTITION BY list (id);
CREATE TABLE
postgres=# ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1);
ALTER TABLE
postgres=# ALTER TABLE r DETACH PARTITION r_1;
ALTER TABLE
postgres=# insert into r_1 values(1,1);
ERROR: insert or update on table "r_1" violates foreign key constraint "r_p_id_fkey"
DETAIL: Key (p_id)=(1) is not present in table "p".
After detach operation, r_1 is normal relation and the inherited foreign key 'r_p_id_fkey' should be removed.
tender wang <tndrwang@gmail.com> 于2023年8月3日周四 17:34写道：
I think the code to determine that fk of a partition is inherited or not is not enough.For example, in this case, foreign key r_1_p_id_fkey1 is not inherited from parent.If conform->conparentid(in DetachPartitionFinalize func) is valid, we should recheck confrelid(pg_constraint) field.I try to fix this problem in the attached patch.Any thoughts.Alvaro Herrera <alvherre@alvh.no-ip.org> 于2023年8月3日周四 17:02写道：On 2023-Aug-03, tender wang wrote:
> I think old "sub-FK" should not be dropped, that will be violates foreign
> key constraint.
Yeah, I've been playing more with the patch and it is definitely not
doing the right things. Just eyeballing the contents of pg_trigger and
pg_constraint for partitions added by ALTER...ATTACH shows that the
catalog contents are inconsistent with those added by CREATE TABLE
PARTITION OF.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
В списке pgsql-hackers по дате отправления: