Hi pg-hackers,
I'm trying to fix some of limitations in table inheritance. My first use
case concerns "referencing" foreign keys in parent table.
The attached patch propagates foreign keys to inherited tables. I'm
automatically cloning foreign keys from parent table into children (with
right dependencies). It's the native implementation of work-around
described in documentation section 5.9.1. (Caveats).
To be honest it's my first pg hack. Thus it might contain (serious)
issues (please be lenient with it).
Furthermore it's not yet documented (TODO !!)
As far as I tested it works pretty well; the internal triggers are
installed on CREATE TABLE .. INHERIT and even after ALTER TABLE .. INHERIT.
If you have few minutes to challenge this hack and give your advices, be
my guest !
Kind regards,
--
Raphael Medaer
Product Development Engineer
Escaux
Escaux, Communication as easy as the web
Chaussée de Bruxelles 408, 1300 Wavre, Belgium
Direct: +3227887564
Main: +3226860900
www.escaux.com <http://www.escaux.com>
------------------------------------------------------------------------
/Dit bericht is onderworpen aan de voorwaarden beschikbaar op onze
website <https://www.escaux.com/docs/ContractInfo.html>.
Ce message est soumis aux conditions disponibles sur notre site web
<https://www.escaux.com/docs/ContractInfo.html>.
This message is subject to the terms and conditions available on our
website <https://www.escaux.com/docs/ContractInfo.html>. /
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 7a6d158f89..7c15c1292b 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -1047,6 +1047,39 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
heap_close(constrRel, RowExclusiveLock);
}
+bool
+relation_constraint_oid_exists(Oid relid, Oid conid)
+{
+ bool exists = false;
+ Relation pg_constraint;
+ HeapTuple tuple;
+ SysScanDesc scan;
+ ScanKeyData key;
+
+ pg_constraint = heap_open(ConstraintRelationId, AccessShareLock);
+
+ ScanKeyInit(&key,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(relid));
+
+ scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,
+ NULL, 1, &key);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ if (HeapTupleGetOid(tuple) == conid) {
+ exists = true;
+ break;
+ }
+ }
+
+ systable_endscan(scan);
+ heap_close(pg_constraint, AccessShareLock);
+
+ return exists;
+}
/*
* get_relation_constraint_oid
diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c
index 2ea05f350b..dbb82f95bc 100644
--- a/src/backend/catalog/pg_depend.c
+++ b/src/backend/catalog/pg_depend.c
@@ -279,6 +279,48 @@ deleteDependencyRecordsForClass(Oid classId, Oid objectId,
return count;
}
+/**
+ *
+ */
+long
+deleteDependencyRecordsForRefObject(Oid classId, Oid objectId, Oid refObjectId)
+{
+ long count = 0;
+ ScanKeyData keys[2];
+ SysScanDesc scan;
+ Relation rel;
+ HeapTuple tuple;
+
+ rel = heap_open(DependRelationId, RowExclusiveLock);
+
+ /* Use index to filter class/object */
+ ScanKeyInit(&keys[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(classId));
+ ScanKeyInit(&keys[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(objectId));
+ scan = systable_beginscan(rel, DependDependerIndexId, true, NULL, 2, keys);
+
+ while(HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ Form_pg_depend depform = (Form_pg_depend) GETSTRUCT(tuple);
+
+ if (depform->refobjid == refObjectId) {
+ CatalogTupleDelete(rel, &tuple->t_self);
+ count++;
+ }
+ }
+
+ systable_endscan(scan);
+ heap_close(rel, RowExclusiveLock);
+
+ return count;
+}
+
+
/*
* Adjust dependency record(s) to point to a different object of the same type
*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d20991a1e5..fbbbf59afa 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -338,6 +338,10 @@ static void validateCheckConstraint(Relation rel, HeapTuple constrtup);
static void validateForeignKeyConstraint(char *conname,
Relation rel, Relation pkrel,
Oid pkindOid, Oid constraintOid);
+static void
+createForeignKeyCheckTriggers(Oid myRelOid, Oid refRelOid,
+ Constraint *fkconstraint, Oid constraintOid,
+ Oid indexOid);
static void ATController(AlterTableStmt *parsetree,
Relation rel, List *cmds, bool recurse, LOCKMODE lockmode);
static void ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
@@ -410,7 +414,7 @@ static ObjectAddress ATAddCheckConstraint(List **wqueue,
LOCKMODE lockmode);
static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab,
Relation rel, Constraint *fkconstraint, Oid parentConstr,
- bool recurse, bool recursing,
+ bool recurse, bool recursing, bool forChild,
LOCKMODE lockmode);
static void ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
@@ -972,6 +976,25 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
+ * If we INHERIT: replicate foreign key constraints from parent.
+ */
+ if (stmt->partbound == NULL) {
+ ListCell *cell;
+ Relation parent;
+
+ foreach(cell, inheritOids) {
+ parent = heap_open(lfirst_oid(cell), RowExclusiveLock);
+
+ CloneForeignKeyConstraints(
+ RelationGetRelid(parent),
+ RelationGetRelid(rel),
+ NULL);
+
+ heap_close(parent, RowExclusiveLock);
+ }
+ }
+
+ /*
* Now add any newly specified column default values and CHECK constraints
* to the new relation. These are passed to us in the form of raw
* parsetrees; we need to transform them to executable expression trees
@@ -7120,7 +7143,7 @@ ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
address = ATAddForeignKeyConstraint(wqueue, tab, rel,
newConstraint, InvalidOid,
- recurse, false,
+ recurse, false, false,
lockmode);
break;
@@ -7277,7 +7300,7 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
static ObjectAddress
ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
Constraint *fkconstraint, Oid parentConstr,
- bool recurse, bool recursing, LOCKMODE lockmode)
+ bool recurse, bool recursing, bool forChild, LOCKMODE lockmode)
{
Relation pkrel;
int16 pkattnum[INDEX_MAX_KEYS];
@@ -7296,6 +7319,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
bool old_check_ok;
ObjectAddress address;
ListCell *old_pfeqop_item = list_head(fkconstraint->old_conpfeqop);
+ List *children;
/*
* Grab ShareRowExclusiveLock on the pk table, so that someone doesn't
@@ -7719,7 +7743,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
childAddr =
ATAddForeignKeyConstraint(wqueue, childtab, partition,
fkconstraint, constrOid,
- recurse, true, lockmode);
+ recurse, true, false, lockmode);
/* Record this constraint as dependent on the parent one */
recordDependencyOn(&childAddr, &address, DEPENDENCY_INTERNAL_AUTO);
@@ -7729,6 +7753,44 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
}
/*
+ * Apply the same foreign key constraint to each inherited table.
+ */
+ children = find_all_inheritors(RelationGetRelid(rel), AccessShareLock, NULL);
+ if (recurse && !forChild && list_length(children)) {
+ ListCell *cell;
+ foreach(cell, children) {
+ Oid childId;
+ Relation child;
+ AlteredTableInfo *childtab;
+ ObjectAddress childAddr;
+
+ childId = lfirst_oid(cell);
+ if (childId == RelationGetRelid(rel))
+ continue;
+
+ child = heap_open(childId, lockmode);
+ ereport(NOTICE,
+ (errmsg("applying same constraint on child \"%s\"",
+ RelationGetRelationName(child))));
+
+ CheckTableNotInUse(child, "ALTER TABLE");
+
+ /* Find or create work queue entry for this table */
+ childtab = ATGetQueueEntry(wqueue, child);
+
+ childAddr =
+ ATAddForeignKeyConstraint(wqueue, childtab, child,
+ fkconstraint, constrOid,
+ recurse, false, true, lockmode);
+
+ /* Record this constraint as dependent on the parent one */
+ recordDependencyOn(&childAddr, &address, DEPENDENCY_INTERNAL_AUTO);
+
+ heap_close(child, NoLock);
+ }
+ }
+
+ /*
* Close pk table, but keep lock until we've committed.
*/
heap_close(pkrel, NoLock);
@@ -11501,6 +11563,9 @@ CreateInheritance(Relation child_rel, Relation parent_rel)
ScanKeyData key;
HeapTuple inheritsTuple;
int32 inhseqno;
+ List *cloned;
+ ListCell *cell;
+
/* Note: get RowExclusiveLock because we will write pg_inherits below. */
catalogRelation = heap_open(InheritsRelationId, RowExclusiveLock);
@@ -11544,6 +11609,26 @@ CreateInheritance(Relation child_rel, Relation parent_rel)
/* Match up the constraints and bump coninhcount as needed */
MergeConstraintsIntoExisting(child_rel, parent_rel);
+ /* Merge also foreign key and validate them */
+ CloneForeignKeyConstraints(
+ RelationGetRelid(parent_rel),
+ RelationGetRelid(child_rel),
+ &cloned);
+ foreach(cell, cloned)
+ {
+ ClonedConstraint *clonedcon = lfirst(cell);
+ Relation rel = heap_open(clonedcon->relid, RowShareLock);
+ Relation refrel = heap_open(clonedcon->refrelid, RowShareLock);
+ validateForeignKeyConstraint(
+ clonedcon->constraint->conname,
+ rel, refrel,
+ clonedcon->conindid,
+ clonedcon->conid);
+ heap_close(rel, RowShareLock);
+ heap_close(refrel, RowShareLock);
+ }
+
+
/*
* OK, it looks valid. Make the catalog entries that show inheritance.
*/
@@ -11799,6 +11884,7 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
HeapTuple child_tuple;
bool found = false;
+
if (parent_con->contype != CONSTRAINT_CHECK)
continue;
@@ -11892,6 +11978,67 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
}
/*
+ * TODO
+ */
+static void
+removeInheritedConstraints(Relation rel, Relation parent_rel) {
+ Relation pg_constraint;
+ ObjectAddress conobj;
+ HeapTuple tuple;
+ ScanKeyData key;
+ SysScanDesc scan;
+
+ pg_constraint = heap_open(ConstraintRelationId, RowShareLock);
+ ScanKeyInit(&key,
+ Anum_pg_constraint_conrelid, BTEqualStrategyNumber,
+ F_OIDEQ, ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,
+ NULL, 1, &key);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
+
+ /* We only want to drop foreign key inherited from (ex)parent table */
+ if (!con->coninhcount
+ || !con->conparentid
+ || !relation_constraint_oid_exists(
+ RelationGetRelid(parent_rel),
+ con->conparentid))
+ continue;
+
+ /*
+ * XXX Result should always be 1, do we have to check it ?
+ *
+ * I would say no. Indeed if the db is corrupted, it will resolve "itself"
+ * otherwise there is no way to remove inherited constraints.
+ */
+ deleteDependencyRecordsForRefObject(
+ ConstraintRelationId, HeapTupleGetOid(tuple), con->conparentid);
+
+ /* Show previous changes for performDeletion procedure */
+ CommandCounterIncrement();
+
+ /* Perform the actual constraint deletion */
+ conobj.classId = ConstraintRelationId;
+ conobj.objectId = HeapTupleGetOid(tuple);
+ conobj.objectSubId = 0;
+
+ performDeletion(&conobj, DROP_RESTRICT, PERFORM_DELETION_INTERNAL);
+ }
+
+ /*
+ * In case of deep inheritance (table inherits rel) the sub-constraints will
+ * be deleted by recursive performDeletion procedure
+ */
+
+ systable_endscan(scan);
+
+ heap_close(pg_constraint, RowShareLock);
+}
+
+/*
* ALTER TABLE NO INHERIT
*
* Return value is the address of the relation that is no longer parent.
@@ -11925,6 +12072,8 @@ ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode)
ObjectAddressSet(address, RelationRelationId,
RelationGetRelid(parent_rel));
+ removeInheritedConstraints(rel, parent_rel);
+
/* keep our lock on the parent relation until commit */
heap_close(parent_rel, NoLock);
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 46c271a46c..69aae5b71e 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -247,6 +247,8 @@ extern long deleteDependencyRecordsFor(Oid classId, Oid objectId,
extern long deleteDependencyRecordsForClass(Oid classId, Oid objectId,
Oid refclassId, char deptype);
+extern long deleteDependencyRecordsForRefObject(Oid classId, Oid objectId, Oid refObjectId);
+
extern long changeDependencyFor(Oid classId, Oid objectId,
Oid refClassId, Oid oldRefObjectId,
Oid newRefObjectId);
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 7c1c0e1db8..30975d5e93 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -248,6 +248,8 @@ extern void AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
Oid newNspId, bool isType, ObjectAddresses *objsMoved);
extern void ConstraintSetParentConstraint(Oid childConstrId,
Oid parentConstrId);
+
+extern bool relation_constraint_oid_exists(Oid relid, Oid conid);
extern Oid get_relation_constraint_oid(Oid relid, const char *conname, bool missing_ok);
extern Bitmapset *get_relation_constraint_attnos(Oid relid, const char *conname,
bool missing_ok, Oid *constraintOid);
