diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index c3039c8..b36ddbf 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1013,7 +1013,7 @@ ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); To add a check constraint only to a table and not to its children: -ALTER TABLE ONLY distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); +ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK ONLY (char_length(zipcode) = 5); (The check constraint will not be inherited by future children, either.) diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 8bd5a92..e15c8aa 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2036,8 +2036,7 @@ AddRelationNewConstraints(Relation rel, List *newColDefaults, List *newConstraints, bool allow_merge, - bool is_local, - bool is_only) + bool is_local) { List *cookedConstraints = NIL; TupleDesc tupleDesc; @@ -2110,7 +2109,7 @@ AddRelationNewConstraints(Relation rel, cooked->skip_validation = false; cooked->is_local = is_local; cooked->inhcount = is_local ? 0 : 1; - cooked->is_only = is_only; + cooked->is_only = false; cookedConstraints = lappend(cookedConstraints, cooked); } @@ -2178,7 +2177,7 @@ AddRelationNewConstraints(Relation rel, * what ATAddCheckConstraint wants.) */ if (MergeWithExistingConstraint(rel, ccname, expr, - allow_merge, is_local, is_only)) + allow_merge, is_local, cdef->is_only)) continue; } else @@ -2225,7 +2224,7 @@ AddRelationNewConstraints(Relation rel, * OK, store it. */ StoreRelCheck(rel, ccname, expr, !cdef->skip_validation, is_local, - is_local ? 0 : 1, is_only); + is_local ? 0 : 1, cdef->is_only); numchecks++; @@ -2237,7 +2236,7 @@ AddRelationNewConstraints(Relation rel, cooked->skip_validation = cdef->skip_validation; cooked->is_local = is_local; cooked->inhcount = is_local ? 0 : 1; - cooked->is_only = is_only; + cooked->is_only = cdef->is_only; cookedConstraints = lappend(cookedConstraints, cooked); } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index a4a3604..0c1aa0d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -661,7 +661,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) */ if (rawDefaults || stmt->constraints) AddRelationNewConstraints(rel, rawDefaults, stmt->constraints, - true, true, false); + true, true); /* * Clean up. We keep lock on new relation (although it shouldn't be @@ -4573,7 +4573,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, * This function is intended for CREATE TABLE, so it processes a * _list_ of defaults, but we just do one. */ - AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, false, true, false); + AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, false, true); /* Make the additional catalog changes visible */ CommandCounterIncrement(); @@ -5015,7 +5015,7 @@ ATExecColumnDefault(Relation rel, const char *colName, * This function is intended for CREATE TABLE, so it processes a * _list_ of defaults, but we just do one. */ - AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, false, true, false); + AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, false, true); } } @@ -5680,16 +5680,11 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, * omitted from the returned list, which is what we want: we do not need * to do any validation work. That can only happen at child tables, * though, since we disallow merging at the top level. - * - * Note: we set is_only based on the recurse flag which is false when - * interpretInhOption() of our statement returns false all the way up - * in AlterTable and gets passed all the way down to here. */ newcons = AddRelationNewConstraints(rel, NIL, list_make1(copyObject(constr)), - recursing, /* allow_merge */ - !recursing, /* is_local */ - !recurse && !recursing); /* is_only */ + recursing, /* allow_merge */ + !recursing); /* is_local */ /* Add each to-be-validated constraint to Phase 3's queue */ foreach(lcon, newcons) @@ -5732,8 +5727,17 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, /* * Adding an ONLY constraint? No need to find our children */ - if (!recurse && !recursing) + if (constr->is_only) return; + /* + * Check if ONLY was specified with ALTER TABLE and not as part of the + * CHECK constraint. Error out if so + */ + if (!recurse && !recursing && !constr->is_only) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("specify ONLY as part of the CHECK constraint definition" + " to create non-inheritable constraints"))); /* * Propagate to children as appropriate. Unlike most other ALTER diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index c94799b..9ccdd16 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2339,6 +2339,7 @@ _copyConstraint(const Constraint *from) COPY_SCALAR_FIELD(deferrable); COPY_SCALAR_FIELD(initdeferred); COPY_LOCATION_FIELD(location); + COPY_SCALAR_FIELD(is_only); COPY_NODE_FIELD(raw_expr); COPY_STRING_FIELD(cooked_expr); COPY_NODE_FIELD(keys); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 9564210..4f89bc0 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2194,6 +2194,7 @@ _equalConstraint(const Constraint *a, const Constraint *b) COMPARE_SCALAR_FIELD(deferrable); COMPARE_SCALAR_FIELD(initdeferred); COMPARE_LOCATION_FIELD(location); + COMPARE_SCALAR_FIELD(is_only); COMPARE_NODE_FIELD(raw_expr); COMPARE_STRING_FIELD(cooked_expr); COMPARE_NODE_FIELD(keys); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 594b3fd..e2a38ae 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2580,6 +2580,7 @@ _outConstraint(StringInfo str, const Constraint *node) case CONSTR_CHECK: appendStringInfo(str, "CHECK"); + WRITE_BOOL_FIELD(is_only); WRITE_NODE_FIELD(raw_expr); WRITE_STRING_FIELD(cooked_expr); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index ae1658a..67d6d49 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -420,7 +420,7 @@ static void processCASbits(int cas_bits, int location, const char *constrType, %type character %type extract_arg %type opt_charset -%type opt_varying opt_timezone +%type opt_varying opt_timezone opt_only %type Iconst SignedIconst %type Sconst comment_text notify_payload @@ -2685,12 +2685,13 @@ ColConstraintElem: n->indexspace = $4; $$ = (Node *)n; } - | CHECK '(' a_expr ')' + | CHECK opt_only '(' a_expr ')' { Constraint *n = makeNode(Constraint); n->contype = CONSTR_CHECK; n->location = @1; - n->raw_expr = $3; + n->is_only = $2; + n->raw_expr = $4; n->cooked_expr = NULL; $$ = (Node *)n; } @@ -2720,6 +2721,7 @@ ColConstraintElem: } ; + /* * ConstraintAttr represents constraint attributes, which we parse as if * they were independent constraint clauses, in order to avoid shift/reduce @@ -2810,14 +2812,15 @@ TableConstraint: ; ConstraintElem: - CHECK '(' a_expr ')' ConstraintAttributeSpec + CHECK opt_only '(' a_expr ')' ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_CHECK; n->location = @1; - n->raw_expr = $3; + n->is_only = $2; + n->raw_expr = $4; n->cooked_expr = NULL; - processCASbits($5, @5, "CHECK", + processCASbits($6, @6, "CHECK", NULL, NULL, &n->skip_validation, yyscanner); n->initially_valid = !n->skip_validation; @@ -2920,6 +2923,10 @@ ConstraintElem: } ; +opt_only: ONLY { $$ = TRUE; } + | /* EMPTY */ { $$ = FALSE; } + ; + opt_column_list: '(' columnList ')' { $$ = $2; } | /*EMPTY*/ { $$ = NIL; } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 64ba8ec..bcd770d 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -1337,10 +1337,13 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, * we don't need the outer parens ... but there are other * cases where we do need 'em. Be conservative for now. * + * Also add ONLY if is_only is true + * * Note that simply checking for leading '(' and trailing ')' * would NOT be good enough, consider "(x > 0) AND (y > 0)". */ - appendStringInfo(&buf, "CHECK (%s)", consrc); + appendStringInfo(&buf, "CHECK%s(%s)", + conForm->conisonly? " ONLY ":" ", consrc); break; } diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index db56910..9b3aeaf 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6089,10 +6089,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) /* * An unvalidated constraint needs to be dumped separately, so * that potentially-violating existing data is loaded before - * the constraint. An ONLY constraint needs to be dumped - * separately too. + * the constraint. */ - constrs[j].separate = !validated || isonly; + constrs[j].separate = !validated; constrs[j].dobj.dump = tbinfo->dobj.dump; @@ -13048,9 +13047,9 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) /* Ignore if not to be dumped separately */ if (coninfo->separate) { - /* 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)); + /* not ONLY since we want it to propagate to children */ + appendPQExpBuffer(q, "ALTER TABLE %s\n", + fmtId(tbinfo->dobj.name)); appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n", fmtId(coninfo->dobj.name), coninfo->condef); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 844a291..d39c9c5 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1812,9 +1812,8 @@ describeOneTableDetails(const char *schemaname, for (i = 0; i < tuples; i++) { /* untranslated contraint name and def */ - printfPQExpBuffer(&buf, " \"%s\"%s%s", + printfPQExpBuffer(&buf, " \"%s\" %s", PQgetvalue(result, i, 0), - (strcmp(PQgetvalue(result, i, 1), "t") == 0) ? " (ONLY) ":" ", PQgetvalue(result, i, 2)); printTableAddFooter(&cont, buf.data); diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index 2055382..a1b565b 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -92,8 +92,7 @@ extern List *AddRelationNewConstraints(Relation rel, List *newColDefaults, List *newConstraints, bool allow_merge, - bool is_local, - bool is_only); + bool is_local); extern void StoreAttrDefault(Relation rel, AttrNumber attnum, Node *expr); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index aaa950d..6583b17 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1528,6 +1528,7 @@ typedef struct Constraint int location; /* token location, or -1 if unknown */ /* Fields used for constraints with expressions (CHECK and DEFAULT): */ + bool is_only; /* is constraint non-inheritable? */ Node *raw_expr; /* expr, as untransformed parse tree */ char *cooked_expr; /* expr, as nodeToString representation */ diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index eba0493..464ff74 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -235,7 +235,7 @@ Check constraints: "con1foo" CHECK (a > 0) Inherits: constraint_rename_test -ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0); +ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK ONLY (b > 0); ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok \d constraint_rename_test @@ -246,7 +246,7 @@ Table "public.constraint_rename_test" b | integer | c | integer | Check constraints: - "con2bar" (ONLY) CHECK (b > 0) + "con2bar" CHECK ONLY (b > 0) "con1foo" CHECK (a > 0) Number of child tables: 1 (Use \d+ to list them.) @@ -275,7 +275,7 @@ Table "public.constraint_rename_test" Indexes: "con3foo" PRIMARY KEY, btree (a) Check constraints: - "con2bar" (ONLY) CHECK (b > 0) + "con2bar" CHECK ONLY (b > 0) "con1foo" CHECK (a > 0) Number of child tables: 1 (Use \d+ to list them.) @@ -643,7 +643,7 @@ drop table atacc1; create table atacc1 (test int); create table atacc2 (test2 int) inherits (atacc1); -- ok: -alter table only atacc1 add constraint foo check (test>0); +alter table atacc1 add constraint foo check only (test>0); -- check constraint is not there on child insert into atacc2 (test) values (-3); -- check constraint is there on parent @@ -652,7 +652,7 @@ ERROR: new row for relation "atacc1" violates check constraint "foo" DETAIL: Failing row contains (-3). insert into atacc1 (test) values (3); -- fail, violating row: -alter table only atacc2 add constraint foo check (test>0); +alter table atacc2 add constraint foo check only (test>0); ERROR: check constraint "foo" is violated by some row drop table atacc2; drop table atacc1; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index d8f20e8c..d1f7b5d 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -598,7 +598,7 @@ 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 p1chk check only (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' order by 1,2; @@ -616,7 +616,7 @@ create table c1 () inherits (p1); --------+---------+----------- ff1 | integer | Check constraints: - "p1chk" (ONLY) CHECK (ff1 > 0) + "p1chk" CHECK ONLY (ff1 > 0) "p2chk" CHECK (ff1 > 10) Number of child tables: 1 (Use \d+ to list them.) diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source index b84d51e..9eb1a29 100644 --- a/src/test/regress/input/constraints.source +++ b/src/test/regress/input/constraints.source @@ -144,6 +144,34 @@ SELECT * FROM INSERT_CHILD; DROP TABLE INSERT_CHILD; -- +-- Check ONLY type of constraints and inheritance +-- + +CREATE TABLE ATACC1 (TEST INT + CHECK ONLY (TEST > 0)); + +CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1); +-- check constraint is not there on child +INSERT INTO ATACC2 (TEST) VALUES (-3); +-- check constraint is there on parent +INSERT INTO ATACC1 (TEST) VALUES (-3); +DROP TABLE ATACC1 CASCADE; + +CREATE TABLE ATACC1 (TEST INT, TEST2 INT + CHECK (TEST > 0), CHECK ONLY (TEST2 > 10)); + +CREATE TABLE ATACC2 () INHERITS (ATACC1); +-- check constraint is there on child +INSERT INTO ATACC2 (TEST) VALUES (-3); +-- check constraint is there on parent +INSERT INTO ATACC1 (TEST) VALUES (-3); +-- check constraint is not there on child +INSERT INTO ATACC2 (TEST2) VALUES (3); +-- check constraint is there on parent +INSERT INTO ATACC1 (TEST2) VALUES (3); +DROP TABLE ATACC1 CASCADE; + +-- -- Check constraints on INSERT INTO -- diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source index 3e02e8d..dc0adee 100644 --- a/src/test/regress/output/constraints.source +++ b/src/test/regress/output/constraints.source @@ -228,6 +228,39 @@ SELECT * FROM INSERT_CHILD; DROP TABLE INSERT_CHILD; -- +-- Check ONLY type of constraints and inheritance +-- +CREATE TABLE ATACC1 (TEST INT + CHECK ONLY (TEST > 0)); +CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1); +-- check constraint is not there on child +INSERT INTO ATACC2 (TEST) VALUES (-3); +-- check constraint is there on parent +INSERT INTO ATACC1 (TEST) VALUES (-3); +ERROR: new row for relation "atacc1" violates check constraint "atacc1_test_check" +DETAIL: Failing row contains (-3). +DROP TABLE ATACC1 CASCADE; +NOTICE: drop cascades to table atacc2 +CREATE TABLE ATACC1 (TEST INT, TEST2 INT + CHECK (TEST > 0), CHECK ONLY (TEST2 > 10)); +CREATE TABLE ATACC2 () INHERITS (ATACC1); +-- check constraint is there on child +INSERT INTO ATACC2 (TEST) VALUES (-3); +ERROR: new row for relation "atacc2" violates check constraint "atacc1_test_check" +DETAIL: Failing row contains (-3, null). +-- check constraint is there on parent +INSERT INTO ATACC1 (TEST) VALUES (-3); +ERROR: new row for relation "atacc1" violates check constraint "atacc1_test_check" +DETAIL: Failing row contains (-3, null). +-- check constraint is not there on child +INSERT INTO ATACC2 (TEST2) VALUES (3); +-- check constraint is there on parent +INSERT INTO ATACC1 (TEST2) VALUES (3); +ERROR: new row for relation "atacc1" violates check constraint "atacc1_test2_check" +DETAIL: Failing row contains (null, 3). +DROP TABLE ATACC1 CASCADE; +NOTICE: drop cascades to table atacc2 +-- -- Check constraints on INSERT INTO -- DELETE FROM INSERT_TBL; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 50c58d2..9edb2df 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -218,7 +218,7 @@ ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fa ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok \d constraint_rename_test \d constraint_rename_test2 -ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0); +ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK ONLY (b > 0); ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok \d constraint_rename_test @@ -500,14 +500,14 @@ drop table atacc1; create table atacc1 (test int); create table atacc2 (test2 int) inherits (atacc1); -- ok: -alter table only atacc1 add constraint foo check (test>0); +alter table atacc1 add constraint foo check only (test>0); -- check constraint is not there on child insert into atacc2 (test) values (-3); -- check constraint is there on parent insert into atacc1 (test) values (-3); insert into atacc1 (test) values (3); -- fail, violating row: -alter table only atacc2 add constraint foo check (test>0); +alter table atacc2 add constraint foo check only (test>0); drop table atacc2; drop table atacc1; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index eec8192..44eda8a 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -140,7 +140,7 @@ 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 p1chk check only (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' order by 1,2;