static bool ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,

Relation rel, HeapTuple contuple, List **otherrelids,

LOCKMODE lockmode);

/* Recursion occurs during execution phase */

ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,

bool recursing, LOCKMODE lockmode)

Relation tgrel;

List *otherrelids = NIL;

ListCell *lc;

tgrel = table_open(TriggerRelationId, RowExclusiveLock);

/*

* If it's not the topmost constraint, raise an error.

*

* Altering a non-topmost constraint leaves some triggers untouched, since

* they are not directly connected to this constraint; also, pg_dump would

* ignore the deferrability status of the individual constraint, since it

* only dumps topmost constraints. Avoid these problems by refusing this

* operation and telling the user to alter the parent constraint instead.

*/

if (OidIsValid(currcon->conparentid))

{

HeapTuple tp;

Oid parent = currcon->conparentid;

char *ancestorname = NULL;

char *ancestortable = NULL;

/* Loop to find the topmost constraint */

while (HeapTupleIsValid(tp = SearchSysCache1(CONSTROID, ObjectIdGetDatum(parent))))

{

Form_pg_constraint contup = (Form_pg_constraint) GETSTRUCT(tp);

/* If no parent, this is the constraint we want */

if (!OidIsValid(contup->conparentid))

{

ancestorname = pstrdup(NameStr(contup->conname));

ancestortable = get_rel_name(contup->conrelid);

ReleaseSysCache(tp);

break;

}

parent = contup->conparentid;

ReleaseSysCache(tp);

}

ereport(ERROR,

(errmsg("cannot alter constraint \"%s\" on relation \"%s\"",

cmdcon->conname, RelationGetRelationName(rel)),

ancestorname && ancestortable ?

errdetail("Constraint \"%s\" is derived from constraint \"%s\" of relation \"%s\".",

cmdcon->conname, ancestorname, ancestortable) : 0,

errhint("You may alter the constraint it derives from, instead.")));

}

/*

* Do the actual catalog work. We can skip changing if already in the

* desired state, but not if a partitioned table: partitions need to be

* processed regardless, in case they had the constraint locally changed.

*/

address = InvalidObjectAddress;

if (currcon->condeferrable != cmdcon->deferrable ||

currcon->condeferred != cmdcon->initdeferred ||

rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)

{

if (ATExecAlterConstrRecurse(cmdcon, conrel, tgrel, rel, contuple,

&otherrelids, lockmode))

ObjectAddressSet(address, ConstraintRelationId, currcon->oid);

}

/*

* ATExecConstrRecurse already invalidated relcache for the relations

* having the constraint itself; here we also invalidate for relations

* that have any triggers that are part of the constraint.

*/

foreach(lc, otherrelids)

CacheInvalidateRelcacheByRelid(lfirst_oid(lc));

systable_endscan(scan);

table_close(tgrel, RowExclusiveLock);

table_close(conrel, RowExclusiveLock);

return address;

}

/*

* Recursive subroutine of ATExecAlterConstraint. Returns true if the

* constraint is altered.

*

* *otherrelids is appended OIDs of relations containing affected triggers.

*

* Note that we must recurse even when the values are correct, in case

* indirect descendants have had their constraints altered locally.

* (This could be avoided if we forbade altering constraints in partitions

* but existing releases don't do that.)

*/

static bool

ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,

Relation rel, HeapTuple contuple, List **otherrelids,

LOCKMODE lockmode)

{

Form_pg_constraint currcon;

Oid conoid;

Oid refrelid;

bool changed = false;

currcon = (Form_pg_constraint) GETSTRUCT(contuple);

conoid = currcon->oid;

refrelid = currcon->confrelid;

/*

* Update pg_constraint with the flags from cmdcon.

*

* If called to modify a constraint that's already in the desired state,

* silently do nothing.

*/

HeapTuple tgtuple;

conoid, 0);

changed = true;

/* Make new constraint flags visible to others */

CacheInvalidateRelcache(rel);

ObjectIdGetDatum(conoid));

HeapTuple copyTuple;

*otherrelids = list_append_unique_oid(*otherrelids,

tgform->tgrelid);

}

/*

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

* recurse and handle every constraint that is a child of this one.

*

* (This assumes that the recurse flag is forcibly set for partitioned

* tables, and not set for legacy inheritance, though we don't check for

* that here.)

*/

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

get_rel_relkind(refrelid) == RELKIND_PARTITIONED_TABLE)

{

ScanKeyData pkey;

SysScanDesc pscan;

HeapTuple childtup;

ScanKeyInit(&pkey,

Anum_pg_constraint_conparentid,

BTEqualStrategyNumber, F_OIDEQ,

ObjectIdGetDatum(conoid));

pscan = systable_beginscan(conrel, ConstraintParentIndexId,

true, NULL, 1, &pkey);

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

Form_pg_constraint childcon = (Form_pg_constraint) GETSTRUCT(childtup);

Relation childrel;

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

ATExecAlterConstrRecurse(cmdcon, conrel, tgrel, childrel, childtup,

otherrelids, lockmode);

table_close(childrel, NoLock);

systable_endscan(pscan);

return changed;

-- Verify constraint deferrability when changed by ALTER

-- Partitioned table at referencing end

CREATE TABLE pt(f1 int, f2 int, f3 int, PRIMARY KEY(f1,f2));

CREATE TABLE ref(f1 int, f2 int, f3 int)

PARTITION BY list(f1);

CREATE TABLE ref1 PARTITION OF ref FOR VALUES IN (1);

CREATE TABLE ref2 PARTITION OF ref FOR VALUES in (2);

ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;

ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey

DEFERRABLE INITIALLY DEFERRED;

INSERT INTO pt VALUES(1,2,3);

INSERT INTO ref VALUES(1,2,3);

BEGIN;

DELETE FROM pt;

DELETE FROM ref;

ABORT;

DROP TABLE pt, ref;

-- Multi-level partitioning at referencing end

CREATE TABLE pt(f1 int, f2 int, f3 int, PRIMARY KEY(f1,f2));

CREATE TABLE ref(f1 int, f2 int, f3 int)

PARTITION BY list(f1);

CREATE TABLE ref1_2 PARTITION OF ref FOR VALUES IN (1, 2) PARTITION BY list (f2);

CREATE TABLE ref1 PARTITION OF ref1_2 FOR VALUES IN (1);

CREATE TABLE ref2 PARTITION OF ref1_2 FOR VALUES IN (2) PARTITION BY list (f2);

CREATE TABLE ref22 PARTITION OF ref2 FOR VALUES IN (2);

ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;

INSERT INTO pt VALUES(1,2,3);

INSERT INTO ref VALUES(1,2,3);

ALTER TABLE ref22 ALTER CONSTRAINT ref_f1_f2_fkey

DEFERRABLE INITIALLY IMMEDIATE; -- fails

ERROR: cannot alter constraint "ref_f1_f2_fkey" on relation "ref22"

DETAIL: Constraint "ref_f1_f2_fkey" is derived from constraint "ref_f1_f2_fkey" of relation "ref".

HINT: You may alter the constraint it derives from, instead.

ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey

DEFERRABLE INITIALLY DEFERRED;

BEGIN;

DELETE FROM pt;

DELETE FROM ref;

ABORT;

DROP TABLE pt, ref;

-- Partitioned table at referenced end

CREATE TABLE pt(f1 int, f2 int, f3 int, PRIMARY KEY(f1,f2))

PARTITION BY LIST(f1);

CREATE TABLE pt1 PARTITION OF pt FOR VALUES IN (1);

CREATE TABLE pt2 PARTITION OF pt FOR VALUES IN (2);

CREATE TABLE ref(f1 int, f2 int, f3 int);

ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;

ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey

DEFERRABLE INITIALLY DEFERRED;

INSERT INTO pt VALUES(1,2,3);

INSERT INTO ref VALUES(1,2,3);

BEGIN;

DELETE FROM pt;

DELETE FROM ref;

ABORT;

DROP TABLE pt, ref;

-- Multi-level partitioning at at referenced end

CREATE TABLE pt(f1 int, f2 int, f3 int, PRIMARY KEY(f1,f2))

PARTITION BY LIST(f1);

CREATE TABLE pt1_2 PARTITION OF pt FOR VALUES IN (1, 2) PARTITION BY LIST (f1);

CREATE TABLE pt1 PARTITION OF pt1_2 FOR VALUES IN (1);

CREATE TABLE pt2 PARTITION OF pt1_2 FOR VALUES IN (2);

CREATE TABLE ref(f1 int, f2 int, f3 int);

ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;

ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey1

DEFERRABLE INITIALLY DEFERRED; -- fails

ERROR: cannot alter constraint "ref_f1_f2_fkey1" on relation "ref"

DETAIL: Constraint "ref_f1_f2_fkey1" is derived from constraint "ref_f1_f2_fkey" of relation "ref".

HINT: You may alter the constraint it derives from, instead.

ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey

DEFERRABLE INITIALLY DEFERRED;

INSERT INTO pt VALUES(1,2,3);

INSERT INTO ref VALUES(1,2,3);

BEGIN;

DELETE FROM pt;

DELETE FROM ref;

ABORT;

DROP TABLE pt, ref;