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

Commitd7b47e5

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 parentd61d9aa commitd7b47e5

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
@@ -101,6 +101,7 @@ typedef struct PrivTarget
101101
#defineCAS_INITIALLY_IMMEDIATE0x04
102102
#defineCAS_INITIALLY_DEFERRED0x08
103103
#defineCAS_NOT_VALID0x10
104+
#defineCAS_NO_INHERIT0x20
104105

105106

106107
#defineparser_yyerror(msg) scanner_yyerror(msg, yyscanner)
@@ -144,7 +145,7 @@ static void SplitColQualList(List *qualList,
144145
core_yyscan_t yyscanner);
145146
staticvoidprocessCASbits(int cas_bits,int location,constchar *constrType,
146147
bool *deferrable,bool *initdeferred,bool *not_valid,
147-
core_yyscan_t yyscanner);
148+
bool *no_inherit,core_yyscan_t yyscanner);
148149

149150
%}
150151

@@ -2709,13 +2710,13 @@ ColConstraintElem:
27092710
n->indexspace =$4;
27102711
$$ = (Node *)n;
27112712
}
2712-
|CHECKopt_no_inherit'('a_expr')'
2713+
|CHECK'('a_expr')'opt_no_inherit
27132714
{
27142715
Constraint *n = makeNode(Constraint);
27152716
n->contype = CONSTR_CHECK;
27162717
n->location =@1;
2717-
n->is_no_inherit =$2;
2718-
n->raw_expr =$4;
2718+
n->is_no_inherit =$5;
2719+
n->raw_expr =$3;
27192720
n->cooked_expr =NULL;
27202721
$$ = (Node *)n;
27212722
}
@@ -2755,10 +2756,10 @@ ColConstraintElem:
27552756
* combinations.
27562757
*
27572758
* See also ConstraintAttributeSpec, which can be used in places where
2758-
* there is no parsing conflict. (Note: currently, NOT VALIDis an allowed
2759-
*clausein ConstraintAttributeSpec, but not here. Someday we might need
2760-
* to allowit here too, but for the moment it doesn't seem useful in the
2761-
* statements that use ConstraintAttr.)
2759+
* there is no parsing conflict. (Note: currently, NOT VALIDand NO INHERIT
2760+
*are allowed clausesin ConstraintAttributeSpec, but not here. Someday we
2761+
*might needto allowthem here too, but for the moment it doesn't seem
2762+
*useful in thestatements that use ConstraintAttr.)
27622763
*/
27632764
ConstraintAttr:
27642765
DEFERRABLE
@@ -2835,17 +2836,16 @@ TableConstraint:
28352836
;
28362837

28372838
ConstraintElem:
2838-
CHECKopt_no_inherit'('a_expr')'ConstraintAttributeSpec
2839+
CHECK'('a_expr')'ConstraintAttributeSpec
28392840
{
28402841
Constraint *n = makeNode(Constraint);
28412842
n->contype = CONSTR_CHECK;
28422843
n->location =@1;
2843-
n->is_no_inherit =$2;
2844-
n->raw_expr =$4;
2844+
n->raw_expr =$3;
28452845
n->cooked_expr =NULL;
2846-
processCASbits($6, @6,"CHECK",
2846+
processCASbits($5, @5,"CHECK",
28472847
NULL,NULL, &n->skip_validation,
2848-
yyscanner);
2848+
&n->is_no_inherit,yyscanner);
28492849
n->initially_valid = !n->skip_validation;
28502850
$$ = (Node *)n;
28512851
}
@@ -2861,7 +2861,7 @@ ConstraintElem:
28612861
n->indexspace =$6;
28622862
processCASbits($7, @7,"UNIQUE",
28632863
&n->deferrable, &n->initdeferred,NULL,
2864-
yyscanner);
2864+
NULL,yyscanner);
28652865
$$ = (Node *)n;
28662866
}
28672867
|UNIQUEExistingIndexConstraintAttributeSpec
@@ -2875,7 +2875,7 @@ ConstraintElem:
28752875
n->indexspace =NULL;
28762876
processCASbits($3, @3,"UNIQUE",
28772877
&n->deferrable, &n->initdeferred,NULL,
2878-
yyscanner);
2878+
NULL,yyscanner);
28792879
$$ = (Node *)n;
28802880
}
28812881
|PRIMARYKEY'('columnList')'opt_definitionOptConsTableSpace
@@ -2890,7 +2890,7 @@ ConstraintElem:
28902890
n->indexspace =$7;
28912891
processCASbits($8, @8,"PRIMARY KEY",
28922892
&n->deferrable, &n->initdeferred,NULL,
2893-
yyscanner);
2893+
NULL,yyscanner);
28942894
$$ = (Node *)n;
28952895
}
28962896
|PRIMARYKEYExistingIndexConstraintAttributeSpec
@@ -2904,7 +2904,7 @@ ConstraintElem:
29042904
n->indexspace =NULL;
29052905
processCASbits($4, @4,"PRIMARY KEY",
29062906
&n->deferrable, &n->initdeferred,NULL,
2907-
yyscanner);
2907+
NULL,yyscanner);
29082908
$$ = (Node *)n;
29092909
}
29102910
|EXCLUDEaccess_method_clause'('ExclusionConstraintList')'
@@ -2922,7 +2922,7 @@ ConstraintElem:
29222922
n->where_clause=$8;
29232923
processCASbits($9, @9,"EXCLUDE",
29242924
&n->deferrable, &n->initdeferred,NULL,
2925-
yyscanner);
2925+
NULL,yyscanner);
29262926
$$ = (Node *)n;
29272927
}
29282928
|FOREIGNKEY'('columnList')'REFERENCESqualified_name
@@ -2939,7 +2939,7 @@ ConstraintElem:
29392939
n->fk_del_action= (char) ($10 &0xFF);
29402940
processCASbits($11, @11,"FOREIGN KEY",
29412941
&n->deferrable, &n->initdeferred,
2942-
&n->skip_validation,
2942+
&n->skip_validation,NULL,
29432943
yyscanner);
29442944
n->initially_valid = !n->skip_validation;
29452945
$$ = (Node *)n;
@@ -4133,7 +4133,7 @@ CreateTrigStmt:
41334133
n->isconstraint =TRUE;
41344134
processCASbits($10, @10,"TRIGGER",
41354135
&n->deferrable, &n->initdeferred,NULL,
4136-
yyscanner);
4136+
NULL,yyscanner);
41374137
n->constrrel =$9;
41384138
$$ = (Node *)n;
41394139
}
@@ -4270,6 +4270,7 @@ ConstraintAttributeElem:
42704270
|INITIALLYIMMEDIATE{$$ = CAS_INITIALLY_IMMEDIATE; }
42714271
|INITIALLYDEFERRED{$$ = CAS_INITIALLY_DEFERRED; }
42724272
|NOTVALID{$$ = CAS_NOT_VALID; }
4273+
|NOINHERIT{$$ = CAS_NO_INHERIT; }
42734274
;
42744275

42754276

@@ -4386,7 +4387,7 @@ CreateAssertStmt:
43864387
n->isconstraint =TRUE;
43874388
processCASbits($8, @8,"ASSERTION",
43884389
&n->deferrable, &n->initdeferred,NULL,
4389-
yyscanner);
4390+
NULL,yyscanner);
43904391

43914392
ereport(ERROR,
43924393
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -13380,7 +13381,7 @@ SplitColQualList(List *qualList,
1338013381
static void
1338113382
processCASbits(int cas_bits, int location, const char *constrType,
1338213383
bool *deferrable, bool *initdeferred, bool *not_valid,
13383-
core_yyscan_t yyscanner)
13384+
bool *no_inherit,core_yyscan_t yyscanner)
1338413385
{
1338513386
/* defaults*/
1338613387
if (deferrable)
@@ -13428,6 +13429,19 @@ processCASbits(int cas_bits, int location, const char *constrType,
1342813429
constrType),
1342913430
parser_errposition(location)));
1343013431
}
13432+
13433+
if (cas_bits & CAS_NO_INHERIT)
13434+
{
13435+
if (no_inherit)
13436+
*no_inherit = true;
13437+
else
13438+
ereport(ERROR,
13439+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13440+
/* translator: %s is CHECK, UNIQUE, or similar*/
13441+
errmsg("%s constraints cannot be marked NO INHERIT",
13442+
constrType),
13443+
parser_errposition(location)));
13444+
}
1343113445
}
1343213446

1343313447
/* 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
@@ -233,7 +233,7 @@ Check constraints:
233233
"con1foo" CHECK (a > 0)
234234
Inherits: constraint_rename_test
235235

236-
ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECKNO INHERIT(b > 0);
236+
ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;
237237
ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
238238
ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
239239
\d constraint_rename_test
@@ -245,7 +245,7 @@ Table "public.constraint_rename_test"
245245
c | integer |
246246
Check constraints:
247247
"con1foo" CHECK (a > 0)
248-
"con2bar" CHECKNO INHERIT(b > 0)
248+
"con2bar" CHECK (b > 0) NO INHERIT
249249
Number of child tables: 1 (Use \d+ to list them.)
250250

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

279279
\d constraint_rename_test2
@@ -635,7 +635,7 @@ drop table atacc1;
635635
create table atacc1 (test int);
636636
create table atacc2 (test2 int) inherits (atacc1);
637637
-- ok:
638-
alter table atacc1 add constraint foo checkno inherit(test>0);
638+
alter table atacc1 add constraint foo check (test>0) no inherit;
639639
-- check constraint is not there on child
640640
insert into atacc2 (test) values (-3);
641641
-- check constraint is there on parent
@@ -644,7 +644,7 @@ ERROR: new row for relation "atacc1" violates check constraint "foo"
644644
DETAIL: Failing row contains (-3).
645645
insert into atacc1 (test) values (3);
646646
-- fail, violating row:
647-
alter table atacc2 add constraint foo checkno inherit(test>0);
647+
alter table atacc2 add constraint foo check (test>0) no inherit;
648648
ERROR: check constraint "foo" is violated by some row
649649
drop table atacc2;
650650
drop table atacc1;

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

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

598598
-- Test non-inheritable parent constraints
599599
create table p1(ff1 int);
600-
alter table p1 add constraint p1chk checkno inherit(ff1 > 0);
600+
alter table p1 add constraint p1chk check (ff1 > 0) no inherit;
601601
alter table p1 add constraint p2chk check (ff1 > 10);
602602
-- connoinherit should be true for NO INHERIT constraint
603603
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;
@@ -615,7 +615,7 @@ create table c1 () inherits (p1);
615615
--------+---------+-----------
616616
ff1 | integer |
617617
Check constraints:
618-
"p1chk" CHECKNO INHERIT(ff1 > 0)
618+
"p1chk" CHECK (ff1 > 0) NO INHERIT
619619
"p2chk" CHECK (ff1 > 10)
620620
Number of child tables: 1 (Use \d+ to list them.)
621621

‎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