Assert(parentRel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);

/*

*Cloneconstraintsfor whichthe parent is on thereferenced side.

*First, cloneconstraintswherethe parent is on thereferencing side.

*/

CloneFkReferenced(parentRel, partitionRel);

CloneFkReferencing(wqueue,parentRel, partitionRel);

/*

*Now cloneconstraintswherethe parent is on thereferencing side.

*Cloneconstraintsfor whichthe parent is on thereferenced side.

*/

CloneFkReferencing(wqueue,parentRel, partitionRel);

CloneFkReferenced(parentRel, partitionRel);

}

/*

* clone those constraints to the given partition. This is to be called

* when the partition is being created or attached.

*

* This ignores self-referencing FKs; those are handled by CloneFkReferencing.

*

* This recurses to partitions, if the relation being attached is partitioned.

* Recursion is done by calling addFkRecurseReferenced.

*/

continue;

}

/*

* Don't clone self-referencing foreign keys, which can be in the

* partitioned table or in the partition-to-be.

*/

if (constrForm->conrelid == RelationGetRelid(parentRel) ||

constrForm->conrelid == RelationGetRelid(partitionRel))

{

ReleaseSysCache(tuple);

continue;

}

/* We need the same lock level that CreateTrigger will acquire */

fkRel = table_open(constrForm->conrelid, ShareRowExclusiveLock);

id_abc bigint

);

ALTER TABLE part3_self_fk ATTACH PARTITION part33_self_fk FOR VALUES FROM (30) TO (40);

SELECT cr.relname, co.conname, co.contype, co.convalidated,

-- verify that this constraint works

INSERT INTO parted_self_fk VALUES (1, NULL), (2, NULL), (3, NULL);

INSERT INTO parted_self_fk VALUES (10, 1), (11, 2), (12, 3) RETURNING tableoid::regclass;

tableoid

---------------

part2_self_fk

part2_self_fk

part2_self_fk

(3 rows)

INSERT INTO parted_self_fk VALUES (4, 5);-- error: referenced doesn't exist

ERROR: insert or update on table "part1_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey"

DETAIL: Key (id_abc)=(5) is not present in table "parted_self_fk".

DELETE FROM parted_self_fk WHERE id = 1 RETURNING *;-- error: reference remains

ERROR: update or delete on table "part1_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey1" on table "parted_self_fk"

DETAIL: Key (id)=(1) is still referenced from table "parted_self_fk".

SELECT cr.relname, co.conname, co.convalidated,

p.conname AS conparent, p.convalidated, cf.relname AS foreignrel

FROM pg_constraint co

JOIN pg_class cr ON cr.oid = co.conrelid

LEFT JOIN pg_class cf ON cf.oid = co.confrelid

LEFT JOIN pg_constraint p ON p.oid = co.conparentid

WHEREcr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))

ORDER BY co.contype,cr.relname, co.conname, p.conname;

relname |conname | contype | convalidated | conparent | convalidated | foreignrel

part2_self_fk | parted_self_fk_id_abc_fkey| f| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part32_self_fk| parted_self_fk_id_abc_fkey| f| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part33_self_fk | parted_self_fk_id_abc_fkey| f| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part3_self_fk| parted_self_fk_id_abc_fkey| f| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

parted_self_fk| parted_self_fk_id_abc_fkey| f| t | | | parted_self_fk

part2_self_fk | part2_self_fk_pkey | p |t |parted_self_fk_pkey| t |

part32_self_fk |part32_self_fk_pkey | p |t |part3_self_fk_pkey| t |

part33_self_fk |part33_self_fk_pkey | p |t |part3_self_fk_pkey| t |

part3_self_fk | part3_self_fk_pkey | p |t |parted_self_fk_pkey| t |

parted_self_fk |parted_self_fk_pkey | p |t | | |

(12 rows)

WHEREco.contype = 'f' AND

cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))

ORDER BY cr.relname, co.conname, p.conname;

part1_self_fk | parted_self_fk_id_abc_fkey| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part2_self_fk| parted_self_fk_id_abc_fkey| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part32_self_fk | parted_self_fk_id_abc_fkey| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part33_self_fk| parted_self_fk_id_abc_fkey| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part3_self_fk| parted_self_fk_id_abc_fkey| t |parted_self_fk_id_abc_fkey | t | parted_self_fk

parted_self_fk | parted_self_fk_id_abc_fkey1 |t |parted_self_fk_id_abc_fkey| t |part1_self_fk

parted_self_fk |parted_self_fk_id_abc_fkey2 |t |parted_self_fk_id_abc_fkey| t |part2_self_fk

parted_self_fk |parted_self_fk_id_abc_fkey3 |t |parted_self_fk_id_abc_fkey| t |part3_self_fk

parted_self_fk | parted_self_fk_id_abc_fkey4 |t |parted_self_fk_id_abc_fkey3| t |part32_self_fk

parted_self_fk |parted_self_fk_id_abc_fkey5 |t |parted_self_fk_id_abc_fkey3 | t| part33_self_fk

(11 rows)

-- detach and re-attach multiple times just to ensure everything is kosher

ALTER TABLE parted_self_fk DETACH PARTITION part2_self_fk;

INSERT INTO part2_self_fk VALUES (16, 9);-- error: referenced doesn't exist

ERROR: insert or update on table "part2_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey"

DETAIL: Key (id_abc)=(9) is not present in table "parted_self_fk".

DELETE FROM parted_self_fk WHERE id = 2 RETURNING *;-- error: reference remains

ERROR: update or delete on table "part1_self_fk" violates foreign key constraint "part2_self_fk_id_abc_fkey" on table "part2_self_fk"

DETAIL: Key (id)=(2) is still referenced from table "part2_self_fk".

ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);

INSERT INTO parted_self_fk VALUES (16, 9);-- error: referenced doesn't exist

ERROR: insert or update on table "part2_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey"

DETAIL: Key (id_abc)=(9) is not present in table "parted_self_fk".

DELETE FROM parted_self_fk WHERE id = 3 RETURNING *;-- error: reference remains

ERROR: update or delete on table "part1_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey1" on table "parted_self_fk"

DETAIL: Key (id)=(3) is still referenced from table "parted_self_fk".

ALTER TABLE parted_self_fk DETACH PARTITION part2_self_fk;

ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);

SELECT cr.relname, co.conname, co.contype, co.convalidated,

ALTER TABLE parted_self_fk DETACH PARTITION part3_self_fk;

ALTER TABLE parted_self_fk ATTACH PARTITION part3_self_fk FOR VALUES FROM (30) TO (40);

ALTER TABLE part3_self_fk DETACH PARTITION part33_self_fk;

ALTER TABLE part3_self_fk ATTACH PARTITION part33_self_fk FOR VALUES FROM (30) TO (40);

SELECT cr.relname, co.conname, co.convalidated,

p.conname AS conparent, p.convalidated, cf.relname AS foreignrel

FROM pg_constraint co

JOIN pg_class cr ON cr.oid = co.conrelid

LEFT JOIN pg_class cf ON cf.oid = co.confrelid

LEFT JOIN pg_constraint p ON p.oid = co.conparentid

WHEREcr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))

ORDER BY co.contype,cr.relname, co.conname, p.conname;

relname |conname | contype | convalidated | conparent | convalidated | foreignrel

part2_self_fk | parted_self_fk_id_abc_fkey| f| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part32_self_fk| parted_self_fk_id_abc_fkey| f| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part33_self_fk | parted_self_fk_id_abc_fkey| f| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part3_self_fk| parted_self_fk_id_abc_fkey| f| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

parted_self_fk| parted_self_fk_id_abc_fkey| f| t | | | parted_self_fk

part2_self_fk | part2_self_fk_pkey | p |t |parted_self_fk_pkey| t |

part32_self_fk |part32_self_fk_pkey | p |t |part3_self_fk_pkey| t |

part33_self_fk |part33_self_fk_pkey | p |t |part3_self_fk_pkey| t |

part3_self_fk | part3_self_fk_pkey | p |t |parted_self_fk_pkey| t |

parted_self_fk |parted_self_fk_pkey | p |t | | |

(12 rows)

WHEREco.contype = 'f' AND

cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))

ORDER BY cr.relname, co.conname, p.conname;

part1_self_fk | parted_self_fk_id_abc_fkey| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part2_self_fk| parted_self_fk_id_abc_fkey| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part32_self_fk | parted_self_fk_id_abc_fkey| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part33_self_fk| parted_self_fk_id_abc_fkey| t | parted_self_fk_id_abc_fkey | t | parted_self_fk

part3_self_fk| parted_self_fk_id_abc_fkey| t |parted_self_fk_id_abc_fkey | t | parted_self_fk

parted_self_fk | parted_self_fk_id_abc_fkey1 |t |parted_self_fk_id_abc_fkey| t |part1_self_fk

parted_self_fk |parted_self_fk_id_abc_fkey2 |t |parted_self_fk_id_abc_fkey| t |part2_self_fk

parted_self_fk |parted_self_fk_id_abc_fkey3 |t |parted_self_fk_id_abc_fkey| t |part3_self_fk

parted_self_fk | parted_self_fk_id_abc_fkey4 |t |parted_self_fk_id_abc_fkey3| t |part32_self_fk

parted_self_fk |parted_self_fk_id_abc_fkey5 |t |parted_self_fk_id_abc_fkey3 | t| part33_self_fk

(11 rows)

-- Leave this table around, for pg_upgrade/pg_dump tests

-- Test creating a constraint at the parent that already exists in partitions.

---------+-------------------------+------------------------+-----------

child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O

child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O

child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | O

child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | O

parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O

parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O

parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | O

parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | O

(6 rows)

(8 rows)

alter table parent disable trigger all;

select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname,

---------+-------------------------+------------------------+-----------

child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | D

child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | D

child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | D

child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | D

parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | D

parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | D

parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | D

parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | D

(6 rows)

(8 rows)

drop table parent, child1;

-- Verify that firing state propagates correctly on creation, too

);

ALTERTABLE part3_self_fk ATTACH PARTITION part33_self_fk FORVALUESFROM (30) TO (40);

SELECTcr.relname,co.conname,co.contype,co.convalidated,

INSERT INTO parted_self_fkVALUES (1,NULL), (2,NULL), (3,NULL);

INSERT INTO parted_self_fkVALUES (10,1), (11,2), (12,3) RETURNING tableoid::regclass;

INSERT INTO parted_self_fkVALUES (4,5);-- error: referenced doesn't exist

DELETEFROM parted_self_fkWHERE id=1 RETURNING*;-- error: reference remains

SELECTcr.relname,co.conname,co.convalidated,

p.connameAS conparent,p.convalidated,cf.relnameAS foreignrel

FROM pg_constraint co

JOIN pg_class crONcr.oid=co.conrelid

LEFT JOIN pg_class cfONcf.oid=co.confrelid

LEFT JOIN pg_constraint pONp.oid=co.conparentid

WHEREcr.oidIN (SELECT relidFROM pg_partition_tree('parted_self_fk'))

ORDER BYco.contype,cr.relname,co.conname,p.conname;

WHEREco.contype='f'AND

cr.oidIN (SELECT relidFROM pg_partition_tree('parted_self_fk'))

ORDER BYcr.relname,co.conname,p.conname;

ALTERTABLE parted_self_fk DETACH PARTITION part2_self_fk;

INSERT INTO part2_self_fkVALUES (16,9);-- error: referenced doesn't exist

DELETEFROM parted_self_fkWHERE id=2 RETURNING*;-- error: reference remains

ALTERTABLE parted_self_fk ATTACH PARTITION part2_self_fk FORVALUESFROM (10) TO (20);

INSERT INTO parted_self_fkVALUES (16,9);-- error: referenced doesn't exist

DELETEFROM parted_self_fkWHERE id=3 RETURNING*;-- error: reference remains

ALTERTABLE parted_self_fk DETACH PARTITION part2_self_fk;

ALTERTABLE parted_self_fk ATTACH PARTITION part2_self_fk FORVALUESFROM (10) TO (20);

SELECTcr.relname,co.conname,co.contype,co.convalidated,

ALTERTABLE parted_self_fk DETACH PARTITION part3_self_fk;

ALTERTABLE parted_self_fk ATTACH PARTITION part3_self_fk FORVALUESFROM (30) TO (40);

ALTERTABLE part3_self_fk DETACH PARTITION part33_self_fk;

ALTERTABLE part3_self_fk ATTACH PARTITION part33_self_fk FORVALUESFROM (30) TO (40);

SELECTcr.relname,co.conname,co.convalidated,

p.connameAS conparent,p.convalidated,cf.relnameAS foreignrel

FROM pg_constraint co

JOIN pg_class crONcr.oid=co.conrelid

LEFT JOIN pg_class cfONcf.oid=co.confrelid

LEFT JOIN pg_constraint pONp.oid=co.conparentid

WHEREcr.oidIN (SELECT relidFROM pg_partition_tree('parted_self_fk'))

ORDER BYco.contype,cr.relname,co.conname,p.conname;

WHEREco.contype='f'AND

cr.oidIN (SELECT relidFROM pg_partition_tree('parted_self_fk'))

ORDER BYcr.relname,co.conname,p.conname;