Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6804325

Browse files
committed
Change syntax of new CHECK NO INHERIT constraints
The initially implemented syntax, "CHECK NO INHERIT (expr)" was notdeemed very good, so switch to "CHECK (expr) NO INHERIT" instead. Thisway it looks similar to SQL-standards compliant constraint attribute.Backport to 9.2 where the new syntax and feature was introduced.Per discussion.
1 parentd86fb72 commit6804325

File tree

12 files changed

+67
-48
lines changed

12 files changed

+67
-48
lines changed

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -495,7 +495,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
495495
<para>
496496
There must also be matching child-table constraints for all
497497
<literal>CHECK</literal> constraints of the parent, except those
498-
marked non-inheritable (that is, created with <literal>ALTER TABLEONLY</literal>)
498+
marked non-inheritable (that is, created with <literal>ALTER TABLE... ADD CONSTRAINT ... NO INHERIT</literal>)
499499
in the parent, which are ignored; all child-table constraints matched
500500
must not be marked non-inheritable.
501501
Currently
@@ -1013,7 +1013,7 @@ ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
10131013
<para>
10141014
To add a check constraint only to a table and not to its children:
10151015
<programlisting>
1016-
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECKNO INHERIT(char_length(zipcode) = 5);
1016+
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
10171017
</programlisting>
10181018
(The check constraint will not be inherited by future children, either.)
10191019
</para>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
4747
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
4848
{ NOT NULL |
4949
NULL |
50-
CHECK[ NO INHERIT ]( <replaceable class="PARAMETER">expression</replaceable> ) |
50+
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |
5151
DEFAULT <replaceable>default_expr</replaceable> |
5252
UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |
5353
PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
@@ -58,7 +58,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
5858
<phrase>and <replaceable class="PARAMETER">table_constraint</replaceable> is:</phrase>
5959

6060
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
61-
{ CHECK[ NO INHERIT ]( <replaceable class="PARAMETER">expression</replaceable> ) |
61+
{ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |
6262
UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
6363
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
6464
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
@@ -417,7 +417,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
417417
</varlistentry>
418418

419419
<varlistentry>
420-
<term><literal>CHECK[ NO INHERIT ]( <replaceable class="PARAMETER">expression</replaceable> )</literal></term>
420+
<term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]</literal></term>
421421
<listitem>
422422
<para>
423423
The <literal>CHECK</> clause specifies an expression producing a

‎doc/src/sgml/release-9.2.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1271,7 +1271,7 @@
12711271
<listitem>
12721272
<para>
12731273
Allow <literal>CHECK</> constraints to be declared <literal>NO
1274-
INHERIT</> (Nikhil Sontakke, Alex Hunsaker)
1274+
INHERIT</> (Nikhil Sontakke, Alex Hunsaker, &Aacute;lvaro Herrera)
12751275
</para>
12761276

12771277
<para>

‎src/backend/commands/typecmds.c

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -921,8 +921,14 @@ DefineDomain(CreateDomainStmt *stmt)
921921

922922
/*
923923
* Check constraints are handled after domain creation, as
924-
* they require the Oid of the domain
924+
* they require the Oid of the domain; at this point we can
925+
* only check that they're not marked NO INHERIT, because
926+
* that would be bogus.
925927
*/
928+
if (constr->is_no_inherit)
929+
ereport(ERROR,
930+
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
931+
errmsg("CHECK constraints for domains cannot be marked NO INHERIT")));
926932
break;
927933

928934
/*

‎src/backend/parser/gram.y

Lines changed: 36 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -100,6 +100,7 @@ typedef struct PrivTarget
100100
#defineCAS_INITIALLY_IMMEDIATE0x04
101101
#defineCAS_INITIALLY_DEFERRED0x08
102102
#defineCAS_NOT_VALID0x10
103+
#defineCAS_NO_INHERIT0x20
103104

104105

105106
#defineparser_yyerror(msg) scanner_yyerror(msg, yyscanner)
@@ -143,7 +144,7 @@ static void SplitColQualList(List *qualList,
143144
core_yyscan_t yyscanner);
144145
staticvoidprocessCASbits(int cas_bits,int location,constchar *constrType,
145146
bool *deferrable,bool *initdeferred,bool *not_valid,
146-
core_yyscan_t yyscanner);
147+
bool *no_inherit,core_yyscan_t yyscanner);
147148

148149
%}
149150

@@ -2701,13 +2702,13 @@ ColConstraintElem:
27012702
n->indexspace =$4;
27022703
$$ = (Node *)n;
27032704
}
2704-
|CHECKopt_no_inherit'('a_expr')'
2705+
|CHECK'('a_expr')'opt_no_inherit
27052706
{
27062707
Constraint *n = makeNode(Constraint);
27072708
n->contype = CONSTR_CHECK;
27082709
n->location =@1;
2709-
n->is_no_inherit =$2;
2710-
n->raw_expr =$4;
2710+
n->is_no_inherit =$5;
2711+
n->raw_expr =$3;
27112712
n->cooked_expr =NULL;
27122713
$$ = (Node *)n;
27132714
}
@@ -2747,10 +2748,10 @@ ColConstraintElem:
27472748
* combinations.
27482749
*
27492750
* See also ConstraintAttributeSpec, which can be used in places where
2750-
* there is no parsing conflict. (Note: currently, NOT VALIDis an allowed
2751-
*clausein ConstraintAttributeSpec, but not here. Someday we might need
2752-
* to allowit here too, but for the moment it doesn't seem useful in the
2753-
* statements that use ConstraintAttr.)
2751+
* there is no parsing conflict. (Note: currently, NOT VALIDand NO INHERIT
2752+
*are allowed clausesin ConstraintAttributeSpec, but not here. Someday we
2753+
*might needto allowthem here too, but for the moment it doesn't seem
2754+
*useful in thestatements that use ConstraintAttr.)
27542755
*/
27552756
ConstraintAttr:
27562757
DEFERRABLE
@@ -2827,17 +2828,16 @@ TableConstraint:
28272828
;
28282829

28292830
ConstraintElem:
2830-
CHECKopt_no_inherit'('a_expr')'ConstraintAttributeSpec
2831+
CHECK'('a_expr')'ConstraintAttributeSpec
28312832
{
28322833
Constraint *n = makeNode(Constraint);
28332834
n->contype = CONSTR_CHECK;
28342835
n->location =@1;
2835-
n->is_no_inherit =$2;
2836-
n->raw_expr =$4;
2836+
n->raw_expr =$3;
28372837
n->cooked_expr =NULL;
2838-
processCASbits($6, @6,"CHECK",
2838+
processCASbits($5, @5,"CHECK",
28392839
NULL,NULL, &n->skip_validation,
2840-
yyscanner);
2840+
&n->is_no_inherit,yyscanner);
28412841
n->initially_valid = !n->skip_validation;
28422842
$$ = (Node *)n;
28432843
}
@@ -2853,7 +2853,7 @@ ConstraintElem:
28532853
n->indexspace =$6;
28542854
processCASbits($7, @7,"UNIQUE",
28552855
&n->deferrable, &n->initdeferred,NULL,
2856-
yyscanner);
2856+
NULL,yyscanner);
28572857
$$ = (Node *)n;
28582858
}
28592859
|UNIQUEExistingIndexConstraintAttributeSpec
@@ -2867,7 +2867,7 @@ ConstraintElem:
28672867
n->indexspace =NULL;
28682868
processCASbits($3, @3,"UNIQUE",
28692869
&n->deferrable, &n->initdeferred,NULL,
2870-
yyscanner);
2870+
NULL,yyscanner);
28712871
$$ = (Node *)n;
28722872
}
28732873
|PRIMARYKEY'('columnList')'opt_definitionOptConsTableSpace
@@ -2882,7 +2882,7 @@ ConstraintElem:
28822882
n->indexspace =$7;
28832883
processCASbits($8, @8,"PRIMARY KEY",
28842884
&n->deferrable, &n->initdeferred,NULL,
2885-
yyscanner);
2885+
NULL,yyscanner);
28862886
$$ = (Node *)n;
28872887
}
28882888
|PRIMARYKEYExistingIndexConstraintAttributeSpec
@@ -2896,7 +2896,7 @@ ConstraintElem:
28962896
n->indexspace =NULL;
28972897
processCASbits($4, @4,"PRIMARY KEY",
28982898
&n->deferrable, &n->initdeferred,NULL,
2899-
yyscanner);
2899+
NULL,yyscanner);
29002900
$$ = (Node *)n;
29012901
}
29022902
|EXCLUDEaccess_method_clause'('ExclusionConstraintList')'
@@ -2914,7 +2914,7 @@ ConstraintElem:
29142914
n->where_clause=$8;
29152915
processCASbits($9, @9,"EXCLUDE",
29162916
&n->deferrable, &n->initdeferred,NULL,
2917-
yyscanner);
2917+
NULL,yyscanner);
29182918
$$ = (Node *)n;
29192919
}
29202920
|FOREIGNKEY'('columnList')'REFERENCESqualified_name
@@ -2931,7 +2931,7 @@ ConstraintElem:
29312931
n->fk_del_action= (char) ($10 &0xFF);
29322932
processCASbits($11, @11,"FOREIGN KEY",
29332933
&n->deferrable, &n->initdeferred,
2934-
&n->skip_validation,
2934+
&n->skip_validation,NULL,
29352935
yyscanner);
29362936
n->initially_valid = !n->skip_validation;
29372937
$$ = (Node *)n;
@@ -4116,7 +4116,7 @@ CreateTrigStmt:
41164116
n->isconstraint =TRUE;
41174117
processCASbits($10, @10,"TRIGGER",
41184118
&n->deferrable, &n->initdeferred,NULL,
4119-
yyscanner);
4119+
NULL,yyscanner);
41204120
n->constrrel =$9;
41214121
$$ = (Node *)n;
41224122
}
@@ -4253,6 +4253,7 @@ ConstraintAttributeElem:
42534253
|INITIALLYIMMEDIATE{$$ = CAS_INITIALLY_IMMEDIATE; }
42544254
|INITIALLYDEFERRED{$$ = CAS_INITIALLY_DEFERRED; }
42554255
|NOTVALID{$$ = CAS_NOT_VALID; }
4256+
|NOINHERIT{$$ = CAS_NO_INHERIT; }
42564257
;
42574258

42584259

@@ -4300,7 +4301,7 @@ CreateAssertStmt:
43004301
n->isconstraint =TRUE;
43014302
processCASbits($8, @8,"ASSERTION",
43024303
&n->deferrable, &n->initdeferred,NULL,
4303-
yyscanner);
4304+
NULL,yyscanner);
43044305

43054306
ereport(ERROR,
43064307
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -13275,7 +13276,7 @@ SplitColQualList(List *qualList,
1327513276
static void
1327613277
processCASbits(int cas_bits, int location, const char *constrType,
1327713278
bool *deferrable, bool *initdeferred, bool *not_valid,
13278-
core_yyscan_t yyscanner)
13279+
bool *no_inherit,core_yyscan_t yyscanner)
1327913280
{
1328013281
/* defaults*/
1328113282
if (deferrable)
@@ -13323,6 +13324,19 @@ processCASbits(int cas_bits, int location, const char *constrType,
1332313324
constrType),
1332413325
parser_errposition(location)));
1332513326
}
13327+
13328+
if (cas_bits & CAS_NO_INHERIT)
13329+
{
13330+
if (no_inherit)
13331+
*no_inherit = true;
13332+
else
13333+
ereport(ERROR,
13334+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13335+
/* translator: %s is CHECK, UNIQUE, or similar*/
13336+
errmsg("%s constraints cannot be marked NO INHERIT",
13337+
constrType),
13338+
parser_errposition(location)));
13339+
}
1332613340
}
1332713341

1332813342
/* parser_init()

‎src/backend/utils/adt/ruleutils.c

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1343,10 +1343,9 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
13431343
* Note that simply checking for leading '(' and trailing ')'
13441344
* would NOT be good enough, consider "(x > 0) AND (y > 0)".
13451345
*/
1346-
appendStringInfo(&buf,"CHECK %s(%s)",
1347-
conForm->connoinherit ?"NO INHERIT " :"",
1348-
consrc);
1349-
1346+
appendStringInfo(&buf,"CHECK (%s)%s",
1347+
consrc,
1348+
conForm->connoinherit ?" NO INHERIT" :"");
13501349
break;
13511350
}
13521351
caseCONSTRAINT_TRIGGER:

‎src/test/regress/expected/alter_table.out

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -235,7 +235,7 @@ Check constraints:
235235
"con1foo" CHECK (a > 0)
236236
Inherits: constraint_rename_test
237237

238-
ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECKNO INHERIT(b > 0);
238+
ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;
239239
ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
240240
ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
241241
\d constraint_rename_test
@@ -247,7 +247,7 @@ Table "public.constraint_rename_test"
247247
c | integer |
248248
Check constraints:
249249
"con1foo" CHECK (a > 0)
250-
"con2bar" CHECKNO INHERIT(b > 0)
250+
"con2bar" CHECK (b > 0) NO INHERIT
251251
Number of child tables: 1 (Use \d+ to list them.)
252252

253253
\d constraint_rename_test2
@@ -276,7 +276,7 @@ Indexes:
276276
"con3foo" PRIMARY KEY, btree (a)
277277
Check constraints:
278278
"con1foo" CHECK (a > 0)
279-
"con2bar" CHECKNO INHERIT(b > 0)
279+
"con2bar" CHECK (b > 0) NO INHERIT
280280
Number of child tables: 1 (Use \d+ to list them.)
281281

282282
\d constraint_rename_test2
@@ -643,7 +643,7 @@ drop table atacc1;
643643
create table atacc1 (test int);
644644
create table atacc2 (test2 int) inherits (atacc1);
645645
-- ok:
646-
alter table atacc1 add constraint foo checkno inherit(test>0);
646+
alter table atacc1 add constraint foo check (test>0) no inherit;
647647
-- check constraint is not there on child
648648
insert into atacc2 (test) values (-3);
649649
-- check constraint is there on parent
@@ -652,7 +652,7 @@ ERROR: new row for relation "atacc1" violates check constraint "foo"
652652
DETAIL: Failing row contains (-3).
653653
insert into atacc1 (test) values (3);
654654
-- fail, violating row:
655-
alter table atacc2 add constraint foo checkno inherit(test>0);
655+
alter table atacc2 add constraint foo check (test>0) no inherit;
656656
ERROR: check constraint "foo" is violated by some row
657657
drop table atacc2;
658658
drop table atacc1;

‎src/test/regress/expected/inherit.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -598,7 +598,7 @@ select * from d;
598598

599599
-- Test non-inheritable parent constraints
600600
create table p1(ff1 int);
601-
alter table p1 add constraint p1chk checkno inherit(ff1 > 0);
601+
alter table p1 add constraint p1chk check (ff1 > 0) no inherit;
602602
alter table p1 add constraint p2chk check (ff1 > 10);
603603
-- connoinherit should be true for NO INHERIT constraint
604604
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit 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);
616616
--------+---------+-----------
617617
ff1 | integer |
618618
Check constraints:
619-
"p1chk" CHECKNO INHERIT(ff1 > 0)
619+
"p1chk" CHECK (ff1 > 0) NO INHERIT
620620
"p2chk" CHECK (ff1 > 10)
621621
Number of child tables: 1 (Use \d+ to list them.)
622622

‎src/test/regress/input/constraints.source

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -148,7 +148,7 @@ DROP TABLE INSERT_CHILD;
148148
--
149149

150150
CREATE TABLE ATACC1 (TEST INT
151-
CHECKNO INHERIT(TEST > 0));
151+
CHECK (TEST > 0) NO INHERIT);
152152

153153
CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1);
154154
-- check constraint is not there on child
@@ -158,7 +158,7 @@ INSERT INTO ATACC1 (TEST) VALUES (-3);
158158
DROP TABLE ATACC1 CASCADE;
159159

160160
CREATE TABLE ATACC1 (TEST INT, TEST2 INT
161-
CHECK (TEST > 0), CHECKNO INHERIT(TEST2 > 10));
161+
CHECK (TEST > 0), CHECK (TEST2 > 10) NO INHERIT);
162162

163163
CREATE TABLE ATACC2 () INHERITS (ATACC1);
164164
-- check constraint is there on child

‎src/test/regress/output/constraints.source

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -231,7 +231,7 @@ DROP TABLE INSERT_CHILD;
231231
-- Check NO INHERIT type of constraints and inheritance
232232
--
233233
CREATE TABLE ATACC1 (TEST INT
234-
CHECKNO INHERIT(TEST > 0));
234+
CHECK (TEST > 0) NO INHERIT);
235235
CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1);
236236
-- check constraint is not there on child
237237
INSERT INTO ATACC2 (TEST) VALUES (-3);
@@ -242,7 +242,7 @@ DETAIL: Failing row contains (-3).
242242
DROP TABLE ATACC1 CASCADE;
243243
NOTICE: drop cascades to table atacc2
244244
CREATE TABLE ATACC1 (TEST INT, TEST2 INT
245-
CHECK (TEST > 0), CHECKNO INHERIT(TEST2 > 10));
245+
CHECK (TEST > 0), CHECK (TEST2 > 10) NO INHERIT);
246246
CREATE TABLE ATACC2 () INHERITS (ATACC1);
247247
-- check constraint is there on child
248248
INSERT INTO ATACC2 (TEST) VALUES (-3);

‎src/test/regress/sql/alter_table.sql

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -218,7 +218,7 @@ ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fa
218218
ALTERTABLE constraint_rename_test RENAMECONSTRAINT con1 TO con1foo;-- ok
219219
\d constraint_rename_test
220220
\d constraint_rename_test2
221-
ALTERTABLE constraint_rename_test ADDCONSTRAINT con2CHECKNO INHERIT(b>0);
221+
ALTERTABLE constraint_rename_test ADDCONSTRAINT con2CHECK (b>0) NO INHERIT;
222222
ALTERTABLE ONLY constraint_rename_test RENAMECONSTRAINT con2 TO con2foo;-- ok
223223
ALTERTABLE constraint_rename_test RENAMECONSTRAINT con2foo TO con2bar;-- ok
224224
\d constraint_rename_test
@@ -500,14 +500,14 @@ drop table atacc1;
500500
createtableatacc1 (testint);
501501
createtableatacc2 (test2int) inherits (atacc1);
502502
-- ok:
503-
altertable atacc1 addconstraint foocheckno inherit(test>0);
503+
altertable atacc1 addconstraint foocheck (test>0) no inherit;
504504
-- check constraint is not there on child
505505
insert into atacc2 (test)values (-3);
506506
-- check constraint is there on parent
507507
insert into atacc1 (test)values (-3);
508508
insert into atacc1 (test)values (3);
509509
-- fail, violating row:
510-
altertable atacc2 addconstraint foocheckno inherit(test>0);
510+
altertable atacc2 addconstraint foocheck (test>0) no inherit;
511511
droptable atacc2;
512512
droptable atacc1;
513513

‎src/test/regress/sql/inherit.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -140,7 +140,7 @@ select * from d;
140140

141141
-- Test non-inheritable parent constraints
142142
createtablep1(ff1int);
143-
altertable p1 addconstraint p1chkcheckno inherit(ff1>0);
143+
altertable p1 addconstraint p1chkcheck (ff1>0) no inherit;
144144
altertable p1 addconstraint p2chkcheck (ff1>10);
145145
-- connoinherit should be true for NO INHERIT constraint
146146
selectpc.relname,pgc.conname,pgc.contype,pgc.conislocal,pgc.coninhcount,pgc.connoinheritfrom pg_classas pcinner join pg_constraintas pgcon (pgc.conrelid=pc.oid)wherepc.relname='p1'order by1,2;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp