Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails - Mailing list pgsql-hackers

From tender wang
Subject Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
Date
Msg-id CAHewXNnZkxCwpOW25FXnOF96-5QBLJ-_q5aipvkYtq0DFJavWw@mail.gmail.com
Whole thread Raw
In response to Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
List pgsql-hackers
Hi Alvaro,
I re-analyzed this issue, and here is my analysis process.
step 1: CREATE TABLE p ( id bigint PRIMARY KEY )
    PARTITION BY list (id);
 step2: CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
 step3: CREATE TABLE r_1 (
    id   bigint PRIMARY KEY,
    p_id bigint NOT NULL,
    FOREIGN KEY (p_id) REFERENCES p (id)
  );
After above step 3 operations, we have below catalog tuples:
postgres=# select oid, relname from pg_class where relname = 'p';
oid | relname
-------+---------
16384 | p
(1 row)
postgres=# select oid, relname from pg_class where relname = 'p_1';
oid | relname
-------+---------
16389 | p_1
(1 row)
postgres=# select oid, relname from pg_class where relname = 'r_1';
oid | relname
-------+---------
16394 | r_1
(1 row)
postgres=# select oid, conname,conrelid,conparentid,confrelid from pg_constraint where conrelid = 16394;
oid | conname | conrelid | conparentid | confrelid
-------+-------------------+----------+-------------+-----------
16397 | r_1_p_id_not_null | 16394 | 0 | 0
16399 | r_1_pkey | 16394 | 0 | 0
16400 | r_1_p_id_fkey | 16394 | 0 | 16384
16403 | r_1_p_id_fkey1 | 16394 | 16400 | 16389
(4 rows)
postgres=# select oid, tgrelid, tgparentid, tgconstrrelid,tgconstrindid,tgconstraint from pg_trigger where tgconstraint = 16403;
oid | tgrelid | tgparentid | tgconstrrelid | tgconstrindid | tgconstraint
-------+---------+------------+---------------+---------------+--------------
16404 | 16389 | 16401 | 16394 | 16392 | 16403
16405 | 16389 | 16402 | 16394 | 16392 | 16403
(2 rows)
postgres=# select oid, tgrelid, tgparentid, tgconstrrelid,tgconstrindid,tgconstraint from pg_trigger where tgconstraint = 16400;
oid | tgrelid | tgparentid | tgconstrrelid | tgconstrindid | tgconstraint
-------+---------+------------+---------------+---------------+--------------
16401 | 16384 | 0 | 16394 | 16387 | 16400
16402 | 16384 | 0 | 16394 | 16387 | 16400
16406 | 16394 | 0 | 16384 | 16387 | 16400
16407 | 16394 | 0 | 16384 | 16387 | 16400
(4 rows)
Because table p is partitioned table and it has one child table p_1. So when r_1 add foreign key constraint, according to addFkRecurseReferenced(),
each partition should have one pg_constraint row(e.g. r_1_p_id_fkey1).
After called addFkRecurseReferenced() in ATAddForeignKeyConstraint(), addFkRecurseReferencing() will be called, in which
it will add INSERT check trigger and UPDATE check trigger for r_1_p_id_fkey but not for r_1_p_id_fkey1.
So when detach r_1 from r, according to DetachPartitionFinalize(), the inherited fks should unlink relationship from parent.
The created INSERT and UPDATE check triggers should unlink relationship link fks. But just like I said above, the r_1_p_id_fkey1
actually doesn't have INSERT check trigger.

I slightly modified the previous patch,but I didn't add test case, because I found another issue.
After done ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1);
I run the oidjoins.sql and has warnings as belwo:
psql:/tender/postgres/src/test/regress/sql/oidjoins.sql:49: WARNING:  FK VIOLATION IN pg_trigger({tgparentid}): ("(0,3)",16401)
psql:/tender/postgres/src/test/regress/sql/oidjoins.sql:49: WARNING:  FK VIOLATION IN pg_trigger({tgparentid}): ("(0,4)",16402)
postgres=# select oid, tgrelid, tgparentid, tgconstrrelid,tgconstrindid,tgconstraint from pg_trigger where oid >= 16384;
  oid  | tgrelid | tgparentid | tgconstrrelid | tgconstrindid | tgconstraint
-------+---------+------------+---------------+---------------+--------------
 16404 |   16389 |      16401 |         16394 |         16392 |        16403
 16405 |   16389 |      16402 |         16394 |         16392 |        16403
 16415 |   16384 |          0 |         16408 |         16387 |        16414
 16416 |   16384 |          0 |         16408 |         16387 |        16414
 16418 |   16389 |      16415 |         16408 |         16392 |        16417
 16419 |   16389 |      16416 |         16408 |         16392 |        16417
 16420 |   16408 |          0 |         16384 |         16387 |        16414
 16421 |   16408 |          0 |         16384 |         16387 |        16414
 16406 |   16394 |      16420 |         16384 |         16387 |        16400
 16407 |   16394 |      16421 |         16384 |         16387 |        16400
(10 rows)
oid = 16401 and oid = 16402 has been deleted.
The two trigger tuples are deleted in tryAttachPartitionForeignKey called by CloneFkReferencing.
/*
* Looks good!  Attach this constraint.  The action triggers in the new
* partition become redundant -- the parent table already has equivalent
* ones, and those will be able to reach the partition.  Remove the ones
* in the partition.  We identify them because they have our constraint
* OID, as well as being on the referenced rel.
*/
The attached patch can't fix above issue. I'm not sure about the impact of this issue. Maybe redundant triggers no need removed.

But it surely make oidjoings.sql fail if I add test case into v2 patch, so I don't add test case in v2 patch.
No test case is not good patch. I just share my idea about this issue. Hope to get your reply.




Alvaro Herrera <alvherre@alvh.no-ip.org> 于2023年10月25日周三 20:13写道:
On 2023-Oct-25, tender wang wrote:

> Hi
>    Is there any conclusion to this issue?

None yet.  I intend to work on this at some point, hopefully soon.

--
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
Attachment

pgsql-hackers by date:

Previous
From: Ashutosh Bapat
Date:
Subject: Re: Use virtual tuple slot for Unique node
Next
From: Dean Rasheed
Date:
Subject: btree_gin: Incorrect leftmost interval value