Following patch implements record of whether a constraint is inherited
or not, and prevents dropping of inherited constraints.
What it doesn't do:
It doesn't yet prevent dropping the parent constraint, which is wrong,
clearly, but what to do about it?
1. make dropping a constraint drop all constraints dependent upon it
(without any explicit cascade)
2. add a new clause to ALTER TABLE .... DROP CONSTRAINT .... CASCADE
I prefer (1), since it is SQL Standard compliant, easier to remember and
automatic de-inheritance is the natural opposite of the automatic
inheritance process.
Current patch passes make check on cvstip, applies cleanly.
Further patch will utilise this new knowledge to reduce the number of
tests made during constraint_exclusion.
Best Regards, Simon Riggs
Index: src/backend/access/common/tupdesc.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/common/tupdesc.c,v
retrieving revision 1.113
diff -c -r1.113 tupdesc.c
*** src/backend/access/common/tupdesc.c 22 Nov 2005 18:17:05 -0000 1.113
--- src/backend/access/common/tupdesc.c 7 Dec 2005 20:15:04 -0000
***************
*** 194,199 ****
--- 194,200 ----
cpy->check[i].ccname = pstrdup(constr->check[i].ccname);
if (constr->check[i].ccbin)
cpy->check[i].ccbin = pstrdup(constr->check[i].ccbin);
+ cpy->check[i].ccinh = constr->check[i].ccinh;
}
}
***************
*** 362,368 ****
for (j = 0; j < n; check2++, j++)
{
if (strcmp(check1->ccname, check2->ccname) == 0 &&
! strcmp(check1->ccbin, check2->ccbin) == 0)
break;
}
if (j >= n)
--- 363,370 ----
for (j = 0; j < n; check2++, j++)
{
if (strcmp(check1->ccname, check2->ccname) == 0 &&
! strcmp(check1->ccbin, check2->ccbin) == 0 &&
! check1->ccinh == check2->ccinh)
break;
}
if (j >= n)
Index: src/backend/catalog/heap.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.293
diff -c -r1.293 heap.c
*** src/backend/catalog/heap.c 22 Nov 2005 18:17:08 -0000 1.293
--- src/backend/catalog/heap.c 7 Dec 2005 20:15:06 -0000
***************
*** 72,78 ****
Oid new_rel_oid,
char new_rel_kind);
static void RelationRemoveInheritance(Oid relid);
! static void StoreRelCheck(Relation rel, char *ccname, char *ccbin);
static void StoreConstraints(Relation rel, TupleDesc tupdesc);
static void SetRelationNumChecks(Relation rel, int numchecks);
--- 72,78 ----
Oid new_rel_oid,
char new_rel_kind);
static void RelationRemoveInheritance(Oid relid);
! static void StoreRelCheck(Relation rel, char *ccname, char *ccbin, bool ccinh);
static void StoreConstraints(Relation rel, TupleDesc tupdesc);
static void SetRelationNumChecks(Relation rel, int numchecks);
***************
*** 1305,1311 ****
* in the pg_class entry for the relation.
*/
static void
! StoreRelCheck(Relation rel, char *ccname, char *ccbin)
{
Node *expr;
char *ccsrc;
--- 1305,1311 ----
* in the pg_class entry for the relation.
*/
static void
! StoreRelCheck(Relation rel, char *ccname, char *ccbin, bool ccinh)
{
Node *expr;
char *ccsrc;
***************
*** 1366,1371 ****
--- 1366,1372 ----
CONSTRAINT_CHECK, /* Constraint Type */
false, /* Is Deferrable */
false, /* Is Deferred */
+ ccinh, /* Is Inherited */
RelationGetRelid(rel), /* relation */
attNos, /* attrs in the constraint */
keycount, /* # attrs in the constraint */
***************
*** 1414,1420 ****
for (i = 0; i < constr->num_check; i++)
StoreRelCheck(rel, constr->check[i].ccname,
! constr->check[i].ccbin);
if (constr->num_check > 0)
SetRelationNumChecks(rel, constr->num_check);
--- 1415,1422 ----
for (i = 0; i < constr->num_check; i++)
StoreRelCheck(rel, constr->check[i].ccname,
! constr->check[i].ccbin,
! constr->check[i].ccinh);
if (constr->num_check > 0)
SetRelationNumChecks(rel, constr->num_check);
***************
*** 1619,1625 ****
/*
* OK, store it.
*/
! StoreRelCheck(rel, ccname, nodeToString(expr));
numchecks++;
--- 1621,1627 ----
/*
* OK, store it.
*/
! StoreRelCheck(rel, ccname, nodeToString(expr), false);
numchecks++;
***************
*** 1782,1788 ****
*/
int
RemoveRelConstraints(Relation rel, const char *constrName,
! DropBehavior behavior)
{
int ndeleted = 0;
Relation conrel;
--- 1784,1790 ----
*/
int
RemoveRelConstraints(Relation rel, const char *constrName,
! DropBehavior behavior, bool isChild)
{
int ndeleted = 0;
Relation conrel;
***************
*** 1813,1818 ****
--- 1815,1828 ----
{
ObjectAddress conobj;
+ /*
+ * Cannot DROP inherited constraints on child tables
+ * unless this is action is part of the ripple down
+ * effect when the top level constraint itself is deleted
+ */
+ if (!isChild && con->coninherited)
+ return -1;
+
conobj.classId = ConstraintRelationId;
conobj.objectId = HeapTupleGetOid(contup);
conobj.objectSubId = 0;
Index: src/backend/catalog/index.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/index.c,v
retrieving revision 1.262
diff -c -r1.262 index.c
*** src/backend/catalog/index.c 22 Nov 2005 18:17:08 -0000 1.262
--- src/backend/catalog/index.c 7 Dec 2005 20:15:08 -0000
***************
*** 644,649 ****
--- 644,650 ----
constraintType,
false, /* isDeferrable */
false, /* isDeferred */
+ false, /* Is Inherited */
heapRelationId,
indexInfo->ii_KeyAttrNumbers,
indexInfo->ii_NumIndexAttrs,
Index: src/backend/catalog/pg_constraint.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/pg_constraint.c,v
retrieving revision 1.28
diff -c -r1.28 pg_constraint.c
*** src/backend/catalog/pg_constraint.c 22 Nov 2005 18:17:08 -0000 1.28
--- src/backend/catalog/pg_constraint.c 7 Dec 2005 20:15:08 -0000
***************
*** 46,51 ****
--- 46,52 ----
char constraintType,
bool isDeferrable,
bool isDeferred,
+ bool isInherited,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
***************
*** 118,123 ****
--- 119,125 ----
values[Anum_pg_constraint_contype - 1] = CharGetDatum(constraintType);
values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable);
values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred);
+ values[Anum_pg_constraint_coninherited - 1] = BoolGetDatum(isInherited);
values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId);
values[Anum_pg_constraint_contypid - 1] = ObjectIdGetDatum(domainId);
values[Anum_pg_constraint_confrelid - 1] = ObjectIdGetDatum(foreignRelId);
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.176
diff -c -r1.176 tablecmds.c
*** src/backend/commands/tablecmds.c 22 Nov 2005 18:17:09 -0000 1.176
--- src/backend/commands/tablecmds.c 7 Dec 2005 20:15:14 -0000
***************
*** 402,407 ****
--- 402,408 ----
{
check[ncheck].ccname = cdef->name;
check[ncheck].ccbin = pstrdup(cdef->cooked_expr);
+ check[ncheck].ccinh = true;
ncheck++;
}
}
***************
*** 3999,4004 ****
--- 4000,4006 ----
CONSTRAINT_FOREIGN,
fkconstraint->deferrable,
fkconstraint->initdeferred,
+ false, /* Is Inherited */
RelationGetRelid(rel),
fkattnum,
numfks,
***************
*** 4658,4667 ****
{
int deleted;
! deleted = RemoveRelConstraints(rel, constrName, behavior);
if (!quiet)
{
/* If zero constraints deleted, complain */
if (deleted == 0)
ereport(ERROR,
--- 4660,4676 ----
{
int deleted;
! deleted = RemoveRelConstraints(rel, constrName, behavior, quiet);
if (!quiet)
{
+ /* If deleted is negative, then constraint deletion not allowed */
+ if (deleted < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("inherited constraint \"%s\" cannot be dropped",
+ constrName)));
+
/* If zero constraints deleted, complain */
if (deleted == 0)
ereport(ERROR,
Index: src/backend/commands/typecmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/typecmds.c,v
retrieving revision 1.85
diff -c -r1.85 typecmds.c
*** src/backend/commands/typecmds.c 22 Nov 2005 18:17:09 -0000 1.85
--- src/backend/commands/typecmds.c 7 Dec 2005 20:15:16 -0000
***************
*** 1880,1885 ****
--- 1880,1886 ----
CONSTRAINT_CHECK, /* Constraint Type */
false, /* Is Deferrable */
false, /* Is Deferred */
+ false, /* Is Inherited */
InvalidOid, /* not a relation constraint */
NULL,
0,
***************
*** 1893,1899 ****
InvalidOid,
expr, /* Tree form check constraint */
ccbin, /* Binary form check constraint */
! ccsrc); /* Source form check constraint */
/*
* Return the compiled constraint expression so the calling routine can
--- 1894,1900 ----
InvalidOid,
expr, /* Tree form check constraint */
ccbin, /* Binary form check constraint */
! ccsrc); /* Source form check constraint */
/*
* Return the compiled constraint expression so the calling routine can
Index: src/backend/utils/cache/relcache.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/cache/relcache.c,v
retrieving revision 1.232
diff -c -r1.232 relcache.c
*** src/backend/utils/cache/relcache.c 22 Nov 2005 18:17:24 -0000 1.232
--- src/backend/utils/cache/relcache.c 7 Dec 2005 20:15:20 -0000
***************
*** 2476,2481 ****
--- 2476,2483 ----
check[found].ccbin = MemoryContextStrdup(CacheMemoryContext,
DatumGetCString(DirectFunctionCall1(textout,
val)));
+
+ check[found].ccinh = DatumGetBool(conform->coninherited);
found++;
}
Index: src/include/access/tupdesc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/tupdesc.h,v
retrieving revision 1.47
diff -c -r1.47 tupdesc.h
*** src/include/access/tupdesc.h 7 Mar 2005 04:42:17 -0000 1.47
--- src/include/access/tupdesc.h 7 Dec 2005 20:15:24 -0000
***************
*** 29,34 ****
--- 29,35 ----
{
char *ccname;
char *ccbin; /* nodeToString representation of expr */
+ bool ccinh;
} ConstrCheck;
/* This structure contains constraints of a tuple */
Index: src/include/catalog/heap.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/heap.h,v
retrieving revision 1.76
diff -c -r1.76 heap.h
*** src/include/catalog/heap.h 15 Oct 2005 02:49:42 -0000 1.76
--- src/include/catalog/heap.h 7 Dec 2005 20:15:24 -0000
***************
*** 75,81 ****
char *attname);
extern int RemoveRelConstraints(Relation rel, const char *constrName,
! DropBehavior behavior);
extern void DeleteRelationTuple(Oid relid);
extern void DeleteAttributeTuples(Oid relid);
--- 75,81 ----
char *attname);
extern int RemoveRelConstraints(Relation rel, const char *constrName,
! DropBehavior behavior, bool isChild);
extern void DeleteRelationTuple(Oid relid);
extern void DeleteAttributeTuples(Oid relid);
Index: src/include/catalog/pg_constraint.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_constraint.h,v
retrieving revision 1.19
diff -c -r1.19 pg_constraint.h
*** src/include/catalog/pg_constraint.h 22 Nov 2005 18:17:30 -0000 1.19
--- src/include/catalog/pg_constraint.h 7 Dec 2005 20:15:24 -0000
***************
*** 49,54 ****
--- 49,55 ----
char contype; /* constraint type; see codes below */
bool condeferrable; /* deferrable constraint? */
bool condeferred; /* deferred by default? */
+ bool coninherited; /* inherited constraint? */
/*
* conrelid and conkey are only meaningful if the constraint applies to a
***************
*** 111,132 ****
* compiler constants for pg_constraint
* ----------------
*/
! #define Natts_pg_constraint 15
#define Anum_pg_constraint_conname 1
#define Anum_pg_constraint_connamespace 2
#define Anum_pg_constraint_contype 3
#define Anum_pg_constraint_condeferrable 4
#define Anum_pg_constraint_condeferred 5
! #define Anum_pg_constraint_conrelid 6
! #define Anum_pg_constraint_contypid 7
! #define Anum_pg_constraint_confrelid 8
! #define Anum_pg_constraint_confupdtype 9
! #define Anum_pg_constraint_confdeltype 10
! #define Anum_pg_constraint_confmatchtype 11
! #define Anum_pg_constraint_conkey 12
! #define Anum_pg_constraint_confkey 13
! #define Anum_pg_constraint_conbin 14
! #define Anum_pg_constraint_consrc 15
/* Valid values for contype */
--- 112,134 ----
* compiler constants for pg_constraint
* ----------------
*/
! #define Natts_pg_constraint 16
#define Anum_pg_constraint_conname 1
#define Anum_pg_constraint_connamespace 2
#define Anum_pg_constraint_contype 3
#define Anum_pg_constraint_condeferrable 4
#define Anum_pg_constraint_condeferred 5
! #define Anum_pg_constraint_coninherited 6
! #define Anum_pg_constraint_conrelid 7
! #define Anum_pg_constraint_contypid 8
! #define Anum_pg_constraint_confrelid 9
! #define Anum_pg_constraint_confupdtype 10
! #define Anum_pg_constraint_confdeltype 11
! #define Anum_pg_constraint_confmatchtype 12
! #define Anum_pg_constraint_conkey 13
! #define Anum_pg_constraint_confkey 14
! #define Anum_pg_constraint_conbin 15
! #define Anum_pg_constraint_consrc 16
/* Valid values for contype */
***************
*** 159,164 ****
--- 161,167 ----
char constraintType,
bool isDeferrable,
bool isDeferred,
+ bool isInherited,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
