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)
+{
+ boolexists = false;
+ Relationpg_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)
+{
+ longcount = 0;
+ ScanKeyData keys[2];
+ SysScanDesc scan;
+ Relationrel;
+ 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 r
