Hi all,
PFA, patch which implements non-inheritable "ONLY" constraints. This
has been achieved by introducing a new column "conisonly" in
pg_constraint catalog. Specification of 'ONLY' in the ALTER TABLE ADD
CONSTRAINT CHECK command is used to set this new column to true.
Constraints which have this column set to true cannot be inherited by
present and future children ever.
The psql and pg_dump binaries have been modified to account for such
persistent non-inheritable check constraints. This patch also has
documentation changes along with relevant changes to the test cases.
The regression runs pass fine with this patch applied.
Comments and further feedback, if any, appreciated.
Regards,
Nikhils
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 5e5f8a7..683ad67 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1995,6 +1995,16 @@
</row>
<row>
+ <entry><structfield>conisonly</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>
+ This constraint is defined locally for the relation. It is a
+ non-inheritable constraint.
+ </entry>
+ </row>
+
+ <row>
<entry><structfield>conkey</structfield></entry>
<entry><type>int2[]</type></entry>
<entry><literal><link
linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</></entry>
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index 4c2a4cd..3ee3ec0 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -984,6 +984,14 @@ ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK
(char_length(zipcode) = 5);
</para>
<para>
+ To add a check constraint only to a table and not to its children:
+<programlisting>
+ALTER TABLE ONLY distributors ADD CONSTRAINT zipchk CHECK
(char_length(zipcode) = 5);
+</programlisting>
+ (The check constraint will not be inherited by future children too.)
+ </para>
+
+ <para>
To remove a check constraint from a table and all its children:
<programlisting>
ALTER TABLE distributors DROP CONSTRAINT zipchk;
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 4399493..1b382b8 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -98,10 +98,10 @@ static Oid AddNewRelationType(const char *typeName,
Oid new_array_type);
static void RelationRemoveInheritance(Oid relid);
static void StoreRelCheck(Relation rel, char *ccname, Node *expr,
- bool is_validated, bool is_local, int inhcount);
+ bool is_validated, bool is_local, int inhcount, bool
is_only);
static void StoreConstraints(Relation rel, List *cooked_constraints);
static bool MergeWithExistingConstraint(Relation rel, char *ccname, Node *expr,
- bool allow_merge, bool
is_local);
+ bool allow_merge, bool
is_local, bool is_only);
static void SetRelationNumChecks(Relation rel, int numchecks);
static Node *cookConstraint(ParseState *pstate,
Node *raw_constraint,
@@ -1860,7 +1860,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum, Node
*expr)
*/
static void
StoreRelCheck(Relation rel, char *ccname, Node *expr,
- bool is_validated, bool is_local, int inhcount)
+ bool is_validated, bool is_local, int inhcount, bool
is_only)
{
char *ccbin;
char *ccsrc;
@@ -1943,7 +1943,8 @@ StoreRelCheck(Relation rel, char *ccname, Node *expr,
ccbin, /* Binary form
of check constraint */
ccsrc, /* Source form
of check constraint */
is_local, /*
conislocal */
- inhcount); /* coninhcount
*/
+ inhcount, /*
coninhcount */
+ is_only);
/* conisonly */
pfree(ccbin);
pfree(ccsrc);
@@ -1984,7 +1985,7 @@ StoreConstraints(Relation rel, List *cooked_constraints)
break;
case CONSTR_CHECK:
StoreRelCheck(rel, con->name, con->expr,
!con->skip_validation,
- con->is_local,
con->inhcount);
+ con->is_local,
con->inhcount, con->is_only);
numchecks++;
break;
default:
@@ -2100,6 +2101,7 @@ AddRelationNewConstraints(Relation rel,
cooked->skip_validation = false;
cooked->is_local = is_local;
cooked->inhcount = is_local ? 0 : 1;
+ cooked->is_only = false;
cookedConstraints = lappend(cookedConstraints, cooked);
}
@@ -2167,7 +2169,7 @@ AddRelationNewConstraints(Relation rel,
* what ATAddCheckConstraint wants.)
*/
if (MergeWithExistingConstraint(rel, ccname, expr,
-
allow_merge, is_local))
+ allow_merge,
is_local, cdef->is_only))
continue;
}
else
@@ -2214,7 +2216,7 @@ AddRelationNewConstraints(Relation rel,
* OK, store it.
*/
StoreRelCheck(rel, ccname, expr, !cdef->skip_validation,
is_local,
- is_local ? 0 : 1);
+ is_local ? 0 : 1, cdef->is_only);
numchecks++;
@@ -2226,6 +2228,7 @@ AddRelationNewConstraints(Relation rel,
cooked->skip_validation = cdef->skip_validation;
cooked->is_local = is_local;
cooked->inhcount = is_local ? 0 : 1;
+ cooked->is_only = cdef->is_only;
cookedConstraints = lappend(cookedConstraints, cooked);
}
@@ -2251,7 +2254,7 @@ AddRelationNewConstraints(Relation rel,
*/
static bool
MergeWithExistingConstraint(Relation rel, char *ccname, Node *expr,
- bool allow_merge, bool
is_local)
+ bool allow_merge, bool
is_local, bool is_only)
{
bool found;
Relation conDesc;
@@ -2313,6 +2316,11 @@ MergeWithExistingConstraint(Relation rel, char *ccname,
Node *expr,
con->conislocal = true;
else
con->coninhcount++;
+ if (is_only)
+ {
+ Assert(is_local);
+ con->conisonly = true;
+ }
simple_heap_update(conDesc, &tup->t_self, tup);
CatalogUpdateIndexes(conDesc, tup);
break;
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 75b4c14..477cad3 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1160,7 +1160,8 @@ index_constraint_create(Relation heapRelation,
NULL,
NULL,
true,
/* islocal */
- 0); /*
inhcount */
+ 0,
/* inhcount */
+ false);
/* isonly */
/*
* Register the index as internally dependent on the constraint.
diff --git a/src/backend/catalog/pg_constraint.c
b/src/backend/catalog/pg_constraint.c
index 6997994..cfe82ea 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -66,7 +66,8 @@ CreateConstraintEntry(const char *constraintName,
const char *conBin,
const char *conSrc,
bool conIsLocal,
- int conInhCount)
+ int conInhCount,
+ bool conIsOnly)
{
Relation conDesc;
Oid conOid;
@@ -169,6 +170,7 @@ CreateConstraintEntry(const char *constraintName,
values[Anum_pg_constraint_confmatchtype - 1] =
CharGetDatum(foreignMatchType);
values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal);
values[Anum_pg_constraint_coninhcount - 1] = Int32GetDatum(conInhCount);
+ values[Anum_pg_constraint_conisonly - 1] = BoolGetDatum(conIsOnly);
if (conkeyArray)
values[Anum_pg_constraint_conkey - 1] =
PointerGetDatum(conkeyArray);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 82bb756..ac2368f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -569,6 +569,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId)
cooked->skip_validation = false;
cooked->is_local = true; /* not used for
defaults */
cooked->inhcount = 0; /* ditto */
+ cooked->is_only = false;
cookedDefaults = lappend(cookedDefaults, cooked);
descriptor->attrs[attnum - 1]->atthasdef = true;
}
@@ -1566,6 +1567,10 @@ MergeAttributes(List *schema, List *supers, char
relpersistence,
char *name = check[i].ccname;
Node *expr;
+ /* ignore if the constraint is non-inheritable
*/
+ if (check[i].cconly)
+ continue;
+
/* adjust varattnos of ccbin here */
expr = stringToNode(check[i].ccbin);
change_varattnos_of_a_node(expr, newattno);
@@ -1584,6 +1589,7 @@ MergeAttributes(List *schema, List *supers, char
relpersistence,
cooked->skip_validation = false;
cooked->is_local = false;
cooked->inhcount = 1;
+ cooked->is_only = false;
constraints = lappend(constraints,
cooked);
}
}
@@ -5433,6 +5439,7 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
ListCell *lcon;
List *children;
ListCell *child;
+ bool skip_children = false;
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
@@ -5499,12 +5506,26 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
/*
* If we are told not to recurse, there had better not be any child
- * tables; else the addition would put them out of step.
+ * tables; else the addition would put them out of step. Unless these
are
+ * ONLY type of constraints of course.
*/
if (children && !recurse)
- ereport(ERROR,
+ {
+ foreach(lcon, newcons)
+ {
+ CookedConstraint *ccon = (CookedConstraint *)
lfirst(lcon);
+
+ if (ccon->is_only)
+ skip_children = true;
+ else
+ skip_children = false;
+ }
+
+ if (!skip_children)
+ ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("constraint must be added to child
tables too")));
+ }
foreach(child, children)
{
@@ -5512,6 +5533,13 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
Relation childrel;
AlteredTableInfo *childtab;
+ /*
+ * Skipping the constraint should be good enough for the
special case.
+ * No need to even release the locks on the children
immediately..
+ */
+ if (skip_children)
+ break;
+
/* find_inheritance_children already got lock */
childrel = heap_open(childrelid, NoLock);
CheckTableNotInUse(childrel, "ALTER TABLE");
@@ -5799,7 +5827,8 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation
rel,
NULL,
NULL,
true,
/* islocal */
- 0);
/* inhcount */
+ 0,
/* inhcount */
+
false); /* isonly */
/*
* Create the triggers that will enforce the constraint.
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 4c31f19..d24fb6b 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -451,7 +451,8 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
NULL,
NULL,
true, /* islocal */
-
0); /* inhcount */
+
0, /* inhcount */
+
false); /* isonly */
}
/*
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 7c27f85..a6c1ab3 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -2542,8 +2542,9 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace,
Oid baseTypeOid,
expr, /* Tree form of check
constraint */
ccbin, /* Binary form
of check constraint */
ccsrc, /* Source form
of check constraint */
- true, /* is local */
- 0); /* inhcount */
+ true, /* is local */
+ 0, /* inhcount */
+ false); /* is only */
/*
* Return the compiled constraint expression so the calling routine can
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 7a51456..e04d082 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2328,6 +2328,7 @@ _copyConstraint(Constraint *from)
COPY_LOCATION_FIELD(location);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
+ COPY_SCALAR_FIELD(is_only);
COPY_NODE_FIELD(keys);
COPY_NODE_FIELD(exclusions);
COPY_NODE_FIELD(options);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 4052a9a..4e13451 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2257,6 +2257,7 @@ _equalConstraint(Constraint *a, Constraint *b)
COMPARE_LOCATION_FIELD(location);
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
+ COMPARE_SCALAR_FIELD(is_only);
COMPARE_NODE_FIELD(keys);
COMPARE_NODE_FIELD(exclusions);
COMPARE_NODE_FIELD(options);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index b5be09a..f0d65b5 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2585,6 +2585,8 @@ _outConstraint(StringInfo str, Constraint *node)
break;
case CONSTR_CHECK:
+ if (node->is_only)
+ appendStringInfo(str, " (ONLY) ");
appendStringInfo(str, "CHECK");
WRITE_NODE_FIELD(raw_expr);
WRITE_STRING_FIELD(cooked_expr);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ac094aa..df0af9d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1621,6 +1621,24 @@ AlterTableStmt:
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_TABLE;
+ /* Check if ONLY was used in the
relation expr */
+ if (n->relation->inhOpt == INH_NO)
+ {
+ ListCell *lcmd;
+ foreach(lcmd, n->cmds)
+ {
+ AlterTableCmd *cmd =
(AlterTableCmd *) lfirst(lcmd);
+
+ /* mark check
constraints as non-inheritable */
+ if (cmd->subtype ==
AT_AddConstraint)
+ {
+ Constraint *n =
(Constraint *)cmd->def;
+
+ if (n->contype
== CONSTR_CHECK)
+
n->is_only = true;
+ }
+ }
+ }
$$ = (Node *)n;
}
| ALTER INDEX qualified_name alter_table_cmds
@@ -2625,6 +2643,7 @@ ColConstraintElem:
n->location = @1;
n->raw_expr = $3;
n->cooked_expr = NULL;
+ n->is_only = false;
$$ = (Node *)n;
}
| DEFAULT b_expr
@@ -2758,6 +2777,7 @@ ConstraintElem:
n->location = @1;
n->raw_expr = $3;
n->cooked_expr = NULL;
+ n->is_only = false;
processCASbits($5, @5, "CHECK",
NULL, NULL,
&n->skip_validation,
yyscanner);
diff --git a/src/backend/utils/cache/relcache.c
b/src/backend/utils/cache/relcache.c
index 809222b..940f122 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3251,6 +3251,7 @@ CheckConstraintFetch(Relation relation)
RelationGetRelationName(relation));
check[found].ccvalid = conform->convalidated;
+ check[found].cconly = conform->conisonly;
check[found].ccname = MemoryContextStrdup(CacheMemoryContext,
NameStr(conform->conname));
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f2ee57c..7f40948 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5895,11 +5895,22 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
tbinfo->dobj.name);
resetPQExpBuffer(q);
- if (g_fout->remoteVersion >= 80400)
+ if (g_fout->remoteVersion > 90100)
{
appendPQExpBuffer(q, "SELECT tableoid, oid,
conname, "
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
- "conislocal "
+ "conislocal,
conisonly "
+ "FROM
pg_catalog.pg_constraint "
+ "WHERE
conrelid = '%u'::pg_catalog.oid "
+ " AND
contype = 'c' "
+ "ORDER BY
conname",
+
tbinfo->dobj.catId.oid);
+ }
+ else if (g_fout->remoteVersion >= 80400)
+ {
+ appendPQExpBuffer(q, "SELECT tableoid, oid,
conname, "
+
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
+ "conislocal,
false AS conisonly "
"FROM
pg_catalog.pg_constraint "
"WHERE
conrelid = '%u'::pg_catalog.oid "
" AND
contype = 'c' "
@@ -5910,7 +5921,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
{
appendPQExpBuffer(q, "SELECT tableoid, oid,
conname, "
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
- "true AS
conislocal "
+ "true AS
conislocal, false AS conisonly "
"FROM
pg_catalog.pg_constraint "
"WHERE
conrelid = '%u'::pg_catalog.oid "
" AND
contype = 'c' "
@@ -5922,7 +5933,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
/* no pg_get_constraintdef, must use consrc */
appendPQExpBuffer(q, "SELECT tableoid, oid,
conname, "
"'CHECK (' ||
consrc || ')' AS consrc, "
- "true AS
conislocal "
+ "true AS
conislocal, false AS conisonly "
"FROM
pg_catalog.pg_constraint "
"WHERE
conrelid = '%u'::pg_catalog.oid "
" AND
contype = 'c' "
@@ -5935,7 +5946,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
appendPQExpBuffer(q, "SELECT tableoid, 0 AS
oid, "
"rcname AS
conname, "
"'CHECK (' ||
rcsrc || ')' AS consrc, "
- "true AS
conislocal "
+ "true AS
conislocal, false AS conisonly "
"FROM
pg_relcheck "
"WHERE
rcrelid = '%u'::oid "
"ORDER BY
rcname",
@@ -5946,7 +5957,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
appendPQExpBuffer(q, "SELECT tableoid, oid, "
"rcname AS
conname, "
"'CHECK (' ||
rcsrc || ')' AS consrc, "
- "true AS
conislocal "
+ "true AS
conislocal, false AS conisonly "
"FROM
pg_relcheck "
"WHERE
rcrelid = '%u'::oid "
"ORDER BY
rcname",
@@ -5959,7 +5970,7 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
"(SELECT oid
FROM pg_class WHERE relname = 'pg_relcheck') AS tableoid, "
"oid, rcname
AS conname, "
"'CHECK (' ||
rcsrc || ')' AS consrc, "
- "true AS
conislocal "
+ "true AS
conislocal, false AS conisonly "
"FROM
pg_relcheck "
"WHERE
rcrelid = '%u'::oid "
"ORDER BY
rcname",
@@ -5999,7 +6010,11 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
constrs[j].condeferrable = false;
constrs[j].condeferred = false;
constrs[j].conislocal = (PQgetvalue(res, j,
4)[0] == 't');
- constrs[j].separate = false;
+ constrs[j].conisonly = (PQgetvalue(res, j,
5)[0] == 't');
+ if (constrs[j].conisonly)
+ constrs[j].separate = true;
+ else
+ constrs[j].separate = false;
constrs[j].dobj.dump = tbinfo->dobj.dump;
@@ -6008,8 +6023,15 @@ getTableAttrs(TableInfo *tblinfo, int numTables)
* --- this is so that any other dependencies
of the
* constraint will be emitted before we try to
create the
* table.
+ *
+ * But if it is an ONLY object, the constraint
has to appear
+ * after the create table.
*/
- addObjectDependency(&tbinfo->dobj,
+ if (constrs[j].conisonly)
+ addObjectDependency(&constrs[j].dobj,
+
tbinfo->dobj.dumpId);
+ else
+ addObjectDependency(&tbinfo->dobj,
constrs[j].dobj.dumpId);
/*
@@ -12823,9 +12845,9 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
/* Ignore if not to be dumped separately */
if (coninfo->separate)
{
- /* not ONLY since we want it to propagate to children */
- appendPQExpBuffer(q, "ALTER TABLE %s\n",
-
fmtId(tbinfo->dobj.name));
+ /* add ONLY if we do not want it to propagate to
children */
+ appendPQExpBuffer(q, "ALTER TABLE %s %s\n",
+ coninfo->conisonly?
"ONLY":"", fmtId(tbinfo->dobj.name));
appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
fmtId(coninfo->dobj.name),
coninfo->condef);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index c95614b..ee25311 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -376,6 +376,7 @@ typedef struct _constraintInfo
bool condeferrable; /* TRUE if constraint is DEFERRABLE */
bool condeferred; /* TRUE if constraint is INITIALLY
DEFERRED */
bool conislocal; /* TRUE if constraint has local
definition */
+ bool conisonly; /* TRUE if constraint is
non-inheritable */
bool separate; /* TRUE if must dump as
separate item */
} ConstraintInfo;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b50c5d6..00c2051 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1729,12 +1729,18 @@ describeOneTableDetails(const char *schemaname,
/* print table (and column) check constraints */
if (tableinfo.checks)
{
+ char *is_only;
+ if (pset.sversion > 90100)
+ is_only = "r.conisonly, ";
+ else
+ is_only = "false AS r.conisonly, ";
+
printfPQExpBuffer(&buf,
- "SELECT r.conname, "
+ "SELECT r.conname, %s"
"pg_catalog.pg_get_constraintdef(r.oid, true)\n"
"FROM
pg_catalog.pg_constraint r\n"
"WHERE r.conrelid = '%s' AND r.contype =
'c'\nORDER BY 1;",
- oid);
+ is_only, oid);
result = PSQLexec(buf.data, false);
if (!result)
goto error_return;
@@ -1747,9 +1753,10 @@ describeOneTableDetails(const char *schemaname,
for (i = 0; i < tuples; i++)
{
/* untranslated contraint name and def
*/
- printfPQExpBuffer(&buf, " \"%s\" %s",
+ printfPQExpBuffer(&buf, "
\"%s\"%s%s",
PQgetvalue(result, i, 0),
-
PQgetvalue(result, i, 1));
+
(strcmp(PQgetvalue(result, i, 1), "t") == 0) ? " (ONLY) ":" ",
+
PQgetvalue(result, i, 2));
printTableAddFooter(&cont, buf.data);
}
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 8b99cb8..d5e1333 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -30,6 +30,7 @@ typedef struct constrCheck
char *ccname;
char *ccbin; /* nodeToString representation
of expr */
bool ccvalid;
+ bool cconly; /* this is a non-inheritable
constraint */
} ConstrCheck;
/* This structure contains constraints of a tuple */
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index aee2d88..d3a588f 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -33,6 +33,7 @@ typedef struct CookedConstraint
bool skip_validation; /* skip validation? (only for
CHECK) */
bool is_local; /* constraint has local
(non-inherited) def */
int inhcount; /* number of times
constraint is inherited */
+ bool is_only; /* constraint has local def and
cannot be inherited */
} CookedConstraint;
extern Relation heap_create(const char *relname,
diff --git a/src/include/catalog/pg_constraint.h
b/src/include/catalog/pg_constraint.h
index 1566af2..b8fb01d 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -88,6 +88,9 @@ CATALOG(pg_constraint,2606)
/* Number of times inherited from direct parent relation(s) */
int4 coninhcount;
+ /* Has a local definition and cannot be inherited */
+ bool conisonly;
+
/*
* VARIABLE LENGTH FIELDS start here. These fields may be NULL, too.
*/
@@ -165,14 +168,15 @@ typedef FormData_pg_constraint *Form_pg_constraint;
#define Anum_pg_constraint_confmatchtype 13
#define Anum_pg_constraint_conislocal 14
#define Anum_pg_constraint_coninhcount 15
-#define Anum_pg_constraint_conkey 16
-#define Anum_pg_constraint_confkey 17
-#define Anum_pg_constraint_conpfeqop 18
-#define Anum_pg_constraint_conppeqop 19
-#define Anum_pg_constraint_conffeqop 20
-#define Anum_pg_constraint_conexclop 21
-#define Anum_pg_constraint_conbin 22
-#define Anum_pg_constraint_consrc 23
+#define Anum_pg_constraint_conisonly 16
+#define Anum_pg_constraint_conkey 17
+#define Anum_pg_constraint_confkey 18
+#define Anum_pg_constraint_conpfeqop 19
+#define Anum_pg_constraint_conppeqop 20
+#define Anum_pg_constraint_conffeqop 21
+#define Anum_pg_constraint_conexclop 22
+#define Anum_pg_constraint_conbin 23
+#define Anum_pg_constraint_consrc 24
/* Valid values for contype */
@@ -227,7 +231,8 @@ extern Oid CreateConstraintEntry(const char *constraintName,
const char *conBin,
const char *conSrc,
bool conIsLocal,
- int conInhCount);
+ int conInhCount,
+ bool conIsOnly);
extern void RemoveConstraintById(Oid conId);
extern void RenameConstraintById(Oid conId, const char *newname);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 92e40d3..d3dc246 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1522,6 +1522,7 @@ typedef struct Constraint
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
Node *raw_expr; /* expr, as untransformed parse tree */
char *cooked_expr; /* expr, as nodeToString representation
*/
+ bool is_only; /* has local definition, cannot
be inherited */
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
List *keys; /* String nodes naming
referenced column(s) */
diff --git a/src/test/regress/expected/alter_table.out
b/src/test/regress/expected/alter_table.out
index 005a88b..d6c1bc1 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -493,16 +493,16 @@ select test2 from atacc2;
drop table atacc2 cascade;
NOTICE: drop cascades to table atacc3
drop table atacc1;
--- adding only to a parent is disallowed as of 8.4
+-- adding only to a parent is allowed as of 9.2
create table atacc1 (test int);
create table atacc2 (test2 int) inherits (atacc1);
--- fail:
+-- ok:
alter table only atacc1 add constraint foo check (test>0);
-ERROR: constraint must be added to child tables too
-- ok:
alter table only atacc2 add constraint foo check (test>0);
--- check constraint not there on parent
+-- check constraint is there on parent
insert into atacc1 (test) values (-3);
+ERROR: new row for relation "atacc1" violates check constraint "foo"
insert into atacc1 (test) values (3);
-- check constraint is there on child
insert into atacc2 (test) values (-3);
diff --git a/src/test/regress/expected/inherit.out
b/src/test/regress/expected/inherit.out
index d59ca44..16abada 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -681,6 +681,41 @@ select * from d;
32 | one | two | three
(1 row)
+-- Test non-inheritable parent constraints
+create table p1(ff1 int);
+alter table only p1 add constraint p1chk check (ff1 > 0);
+alter table p1 add constraint p2chk check (ff1 > 10);
+-- conisonly should be true for ONLY constraint
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.conisonly from pg_class as pc inner join pg_constraint as pgc on
(pgc.conrelid = pc.oid) where pc.relname = 'p1';
+ relname | conname | contype | conislocal | coninhcount | conisonly
+---------+---------+---------+------------+-------------+-----------
+ p1 | p1chk | c | t | 0 | t
+ p1 | p2chk | c | t | 0 | f
+(2 rows)
+
+-- Test that child does not inherit ONLY constraints
+create table c1 () inherits (p1);
+\d p1
+ Table "public.p1"
+ Column | Type | Modifiers
+--------+---------+-----------
+ ff1 | integer |
+Check constraints:
+ "p1chk" (ONLY) CHECK (ff1 > 0)
+ "p2chk" CHECK (ff1 > 10)
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d c1
+ Table "public.c1"
+ Column | Type | Modifiers
+--------+---------+-----------
+ ff1 | integer |
+Check constraints:
+ "p2chk" CHECK (ff1 > 10)
+Inherits: p1
+
+drop table p1 cascade;
+NOTICE: drop cascades to table c1
-- Tests for casting between the rowtypes of parent and child
-- tables. See the pgsql-hackers thread beginning Dec. 4/04
create table base (i integer);
diff --git a/src/test/regress/sql/alter_table.sql
b/src/test/regress/sql/alter_table.sql
index 95e898c..61d63d5 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -450,15 +450,15 @@ select test2 from atacc2;
drop table atacc2 cascade;
drop table atacc1;
--- adding only to a parent is disallowed as of 8.4
+-- adding only to a parent is allowed as of 9.2
create table atacc1 (test int);
create table atacc2 (test2 int) inherits (atacc1);
--- fail:
+-- ok:
alter table only atacc1 add constraint foo check (test>0);
-- ok:
alter table only atacc2 add constraint foo check (test>0);
--- check constraint not there on parent
+-- check constraint is there on parent
insert into atacc1 (test) values (-3);
insert into atacc1 (test) values (3);
-- check constraint is there on child
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 3087a14..9e992ab 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -188,6 +188,20 @@ insert into d values('test','one','two','three');
alter table a alter column aa type integer using bit_length(aa);
select * from d;
+-- Test non-inheritable parent constraints
+create table p1(ff1 int);
+alter table only p1 add constraint p1chk check (ff1 > 0);
+alter table p1 add constraint p2chk check (ff1 > 10);
+-- conisonly should be true for ONLY constraint
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.conisonly from pg_class as pc inner join pg_constraint as pgc on
(pgc.conrelid = pc.oid) where pc.relname = 'p1';
+
+-- Test that child does not inherit ONLY constraints
+create table c1 () inherits (p1);
+\d p1
+\d c1
+
+drop table p1 cascade;
+
-- Tests for casting between the rowtypes of parent and child
-- tables. See the pgsql-hackers thread beginning Dec. 4/04
create table base (i integer);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
