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

Hi Alvaro,

I re-analyzed this issue, and here is my analysis process.

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.