<para>

Additional restrictions apply when unique or primary key constraints

are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.

Also, foreign key constraints on partitioned

tables may not be declared <literal>NOT VALID</literal> at present.

</para>

</listitem>

Oid indexOid,

Oid parentDelTrigger, Oid parentUpdTrigger,

Oid *deleteTrigOid, Oid *updateTrigOid);

static bool tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,

Oid partRelid,

static bool tryAttachPartitionForeignKey(List **wqueue,

ForeignKeyCacheInfo *fk,

Relation partition,

Oid parentConstrOid, int numfks,

AttrNumber *mapped_conkey, AttrNumber *confkey,

Oid *conpfeqop,

* Validity checks (permission checks wait till we have the column

* numbers)

*/

if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)

{

if (!recurse)

ereport(ERROR,

(errcode(ERRCODE_WRONG_OBJECT_TYPE),

errmsg("cannot use ONLY for foreign key on partitioned table \"%s\" referencing relation \"%s\"",

RelationGetRelationName(rel),

RelationGetRelationName(pkrel))));

if (fkconstraint->skip_validation && !fkconstraint->initially_valid)

ereport(ERROR,

(errcode(ERRCODE_WRONG_OBJECT_TYPE),

errmsg("cannot add NOT VALID foreign key on partitioned table \"%s\" referencing relation \"%s\"",

RelationGetRelationName(rel),

RelationGetRelationName(pkrel)),

errdetail("This feature is not yet supported on partitioned tables.")));

}

if (!recurse && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)

ereport(ERROR,

errcode(ERRCODE_WRONG_OBJECT_TYPE),

errmsg("cannot use ONLY for foreign key on partitioned table \"%s\" referencing relation \"%s\"",

RelationGetRelationName(rel),

RelationGetRelationName(pkrel)));

if (pkrel->rd_rel->relkind != RELKIND_RELATION &&

pkrel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)

*/

for (int i = 0; i < pd->nparts; i++)

{

OidpartitionId = pd->oids[i];

Relationpartition = table_open(partitionId, lockmode);

Relationpartition = table_open(pd->oids[i], lockmode);

List *partFKs;

AttrMap *attmap;

AttrNumbermapped_fkattnum[INDEX_MAX_KEYS];

boolattached;

ObjectAddress address;

ListCell *cell;

CheckAlterTableIsSafe(partition);

/* Check whether an existing constraint can be repurposed */

partFKs = copyObject(RelationGetFKeyList(partition));

attached = false;

foreach(cell, partFKs)

foreach_node(ForeignKeyCacheInfo, fk, partFKs)

{

ForeignKeyCacheInfo *fk;

fk = lfirst_node(ForeignKeyCacheInfo, cell);

if (tryAttachPartitionForeignKey(fk,

partitionId,

if (tryAttachPartitionForeignKey(wqueue,

fk,

partition,

parentConstr,

numfks,

mapped_fkattnum,

{

ForeignKeyCacheInfo *fk = lfirst_node(ForeignKeyCacheInfo, lc);

if (tryAttachPartitionForeignKey(fk,

RelationGetRelid(partRel),

if (tryAttachPartitionForeignKey(wqueue,

fk,

partRel,

parentConstrOid,

numfks,

mapped_conkey,

* return false.

*/

static bool

tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,

Oid partRelid,

tryAttachPartitionForeignKey(List **wqueue,

ForeignKeyCacheInfo *fk,

Relation partition,

Oid parentConstrOid,

int numfks,

AttrNumber *mapped_conkey,

Form_pg_constraint parentConstr;

HeapTuplepartcontup;

Form_pg_constraint partConstr;

boolqueueValidation;

ScanKeyData key;

SysScanDesc scan;

HeapTupletrigtup;

}

}

/*

* Looks good so far; do some more extensive checks. Presumably the check

* for 'convalidated' could be dropped, since we don't really care about

* that, but let's be careful for now.

*/

partcontup = SearchSysCache1(CONSTROID,

ObjectIdGetDatum(fk->conoid));

/* Looks good so far; perform more extensive checks. */

partcontup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));

if (!HeapTupleIsValid(partcontup))

elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);

partConstr = (Form_pg_constraint) GETSTRUCT(partcontup);

if (OidIsValid(partConstr->conparentid) ||

!partConstr->convalidated ||

partConstr->condeferrable != parentConstr->condeferrable ||

partConstr->condeferred != parentConstr->condeferred ||

partConstr->confupdtype != parentConstr->confupdtype ||

return false;

}

/*

* Will we need to validate this constraint? A valid parent constraint

* implies that all child constraints have been validated, so if this one

* isn't, we must trigger phase 3 validation.

*/

queueValidation = parentConstr->convalidated && !partConstr->convalidated;

ReleaseSysCache(partcontup);

ReleaseSysCache(parentConstrTup);

systable_endscan(scan);

ConstraintSetParentConstraint(fk->conoid, parentConstrOid, partRelid);

ConstraintSetParentConstraint(fk->conoid, parentConstrOid,

RelationGetRelid(partition));

/*

* Like the constraint, attach partition's "check" triggers to the

&insertTriggerOid, &updateTriggerOid);

Assert(OidIsValid(insertTriggerOid) && OidIsValid(parentInsTrigger));

TriggerSetParentTrigger(trigrel, insertTriggerOid, parentInsTrigger,

partRelid);

RelationGetRelid(partition));

Assert(OidIsValid(updateTriggerOid) && OidIsValid(parentUpdTrigger));

TriggerSetParentTrigger(trigrel, updateTriggerOid, parentUpdTrigger,

partRelid);

RelationGetRelid(partition));

/*

* If the referenced table is partitioned, then the partition we're

table_close(pg_constraint, RowShareLock);

}

CommandCounterIncrement();

/* If validation is needed, put it in the queue now. */

if (queueValidation)

{

Relationconrel;

/*

* We updated this pg_constraint row above to set its parent;

* validating it will cause its convalidated flag to change, so we

* need CCI here. XXX it might work better to effect the convalidated

* changes for all constraints together during phase 3, but that

* requires more invasive code surgery.

*/

CommandCounterIncrement();

conrel = table_open(ConstraintRelationId, RowExclusiveLock);

partcontup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));

if (!HeapTupleIsValid(partcontup))

elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);

/* Use the same lock as for AT_ValidateConstraint */

QueueFKConstraintValidation(wqueue, conrel, partition, partcontup,

ShareUpdateExclusiveLock);

ReleaseSysCache(partcontup);

table_close(conrel, RowExclusiveLock);

}

return true;

}

*

* Add an entry to the wqueue to validate the given foreign key constraint in

* Phase 3 and update the convalidated field in the pg_constraint catalog

* for the specified relation.

* for the specified relation and all its children.

*/

static void

QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,

con = (Form_pg_constraint) GETSTRUCT(contuple);

Assert(con->contype == CONSTRAINT_FOREIGN);

Assert(!con->convalidated);

if (rel->rd_rel->relkind == RELKIND_RELATION)

{

}

/*

*We disallow creating invalid foreign keys to or frompartitioned

*tables, so ignoring the recursion bit isokay.

*If the table at either end of the constraint ispartitioned, we need to

*recurse and handle every constraint that isa child of this constraint.

*/

if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||

get_rel_relkind(con->confrelid) == RELKIND_PARTITIONED_TABLE)

{

ScanKeyData pkey;

SysScanDesc pscan;

HeapTuplechildtup;

ScanKeyInit(&pkey,

Anum_pg_constraint_conparentid,

BTEqualStrategyNumber, F_OIDEQ,

ObjectIdGetDatum(con->oid));

pscan = systable_beginscan(conrel, ConstraintParentIndexId,

true, NULL, 1, &pkey);

while (HeapTupleIsValid(childtup = systable_getnext(pscan)))

{

Form_pg_constraint childcon;

Relationchildrel;

childcon = (Form_pg_constraint) GETSTRUCT(childtup);

/*

* If the child constraint has already been validated, no further

* action is required for it or its descendants, as they are all

* valid.

*/

if (childcon->convalidated)

continue;

childrel = table_open(childcon->conrelid, lockmode);

QueueFKConstraintValidation(wqueue, conrel, childrel, childtup,

lockmode);

table_close(childrel, NoLock);

}

systable_endscan(pscan);

}

/*

* Now update the catalog, while we have the door open.

ALTER TABLE ONLY fk_partitioned_fk ADD FOREIGN KEY (a, b)

REFERENCES fk_notpartitioned_pk;

ERROR: cannot use ONLY for foreign key on partitioned table "fk_partitioned_fk" referencing relation "fk_notpartitioned_pk"

-- Adding a NOT VALID foreign key on a partitioned table referencing

-- a non-partitioned table fails.

ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)

REFERENCES fk_notpartitioned_pk NOT VALID;

ERROR: cannot add NOT VALID foreign key on partitioned table "fk_partitioned_fk" referencing relation "fk_notpartitioned_pk"

DETAIL: This feature is not yet supported on partitioned tables.

-- these inserts, targeting both the partition directly as well as the

-- partitioned table, should all fail

INSERT INTO fk_partitioned_fk (a,b) VALUES (500, 501);

ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key constraint "fk_partitioned_fk_a_fkey" on table "fk_partitioned_fk"

DETAIL: Key (a)=(1) is still referenced from table "fk_partitioned_fk".

DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;

-- NOT VALID foreign keys on partitioned table

CREATE TABLE fk_notpartitioned_pk (a int, b int, PRIMARY KEY (a, b));

CREATE TABLE fk_partitioned_fk (b int, a int) PARTITION BY RANGE (a, b);

ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT VALID;

-- Attaching a child table with the same valid foreign key constraint.

CREATE TABLE fk_partitioned_fk_1 (a int, b int);

ALTER TABLE fk_partitioned_fk_1 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk;

ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000);

-- Child constraint will remain valid.

SELECT conname, convalidated, conrelid::regclass FROM pg_constraint

WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid;

conname | convalidated | conrelid

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

fk_partitioned_fk_a_b_fkey | f | fk_partitioned_fk

fk_partitioned_fk_1_a_b_fkey | t | fk_partitioned_fk_1

(2 rows)

-- Validate the constraint

ALTER TABLE fk_partitioned_fk VALIDATE CONSTRAINT fk_partitioned_fk_a_b_fkey;

-- All constraints are now valid.

SELECT conname, convalidated, conrelid::regclass FROM pg_constraint

WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid;

conname | convalidated | conrelid

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

fk_partitioned_fk_a_b_fkey | t | fk_partitioned_fk

fk_partitioned_fk_1_a_b_fkey | t | fk_partitioned_fk_1

(2 rows)

-- Attaching a child with a NOT VALID constraint.

CREATE TABLE fk_partitioned_fk_2 (a int, b int);

INSERT INTO fk_partitioned_fk_2 VALUES(1000, 1000); -- doesn't exist in referenced table

ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT VALID;

-- It will fail because the attach operation implicitly validates the data.

ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);

ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"

DETAIL: Key (a, b)=(1000, 1000) is not present in table "fk_notpartitioned_pk".

-- Remove the invalid data and try again.

TRUNCATE fk_partitioned_fk_2;

ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);

-- The child constraint will also be valid.

SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_partitioned_fk_2'::regclass;

conname | convalidated

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

fk_partitioned_fk_2_a_b_fkey | t

(1 row)

-- Test case where the child constraint is invalid, the grandchild constraint

-- is valid, and the validation for the grandchild should be skipped when a

-- valid constraint is applied to the top parent.

CREATE TABLE fk_partitioned_fk_3 (a int, b int) PARTITION BY RANGE (a, b);

ALTER TABLE fk_partitioned_fk_3 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT VALID;

CREATE TABLE fk_partitioned_fk_3_1 (a int, b int);

ALTER TABLE fk_partitioned_fk_3_1 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk;

ALTER TABLE fk_partitioned_fk_3 ATTACH PARTITION fk_partitioned_fk_3_1 FOR VALUES FROM (2000,2000) TO (3000,3000);

ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3 FOR VALUES FROM (2000,2000) TO (3000,3000);

-- All constraints are now valid.

SELECT conname, convalidated, conrelid::regclass FROM pg_constraint

WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid;

conname | convalidated | conrelid

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

fk_partitioned_fk_a_b_fkey | t | fk_partitioned_fk

fk_partitioned_fk_1_a_b_fkey | t | fk_partitioned_fk_1

fk_partitioned_fk_2_a_b_fkey | t | fk_partitioned_fk_2

fk_partitioned_fk_3_a_b_fkey | t | fk_partitioned_fk_3

fk_partitioned_fk_3_1_a_b_fkey | t | fk_partitioned_fk_3_1

(5 rows)

DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk;

-- NOT VALID foreign key on a non-partitioned table referencing a partitioned table

CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);

CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);

CREATE TABLE fk_notpartitioned_fk (b int, a int);

ALTER TABLE fk_notpartitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT VALID;

-- Constraint will be invalid.

SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_notpartitioned_fk'::regclass;

conname | convalidated

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

fk_notpartitioned_fk_a_b_fkey | f

fk_notpartitioned_fk_a_b_fkey1 | f

(2 rows)

ALTER TABLE fk_notpartitioned_fk VALIDATE CONSTRAINT fk_notpartitioned_fk_a_b_fkey;

-- All constraints are now valid.

SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_notpartitioned_fk'::regclass;

conname | convalidated

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

fk_notpartitioned_fk_a_b_fkey | t

fk_notpartitioned_fk_a_b_fkey1 | t

(2 rows)

DROP TABLE fk_notpartitioned_fk, fk_partitioned_pk;

-- Test some other exotic foreign key features: MATCH SIMPLE, ON UPDATE/DELETE

-- actions

CREATE TABLE fk_notpartitioned_pk (a int, b int, primary key (a, b));