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

Commit4a02af8

Browse files
committed
Simplify syntax for ALTER TABLE ALTER CONSTRAINT NO INHERIT
Commitd45597f introduced the ability to change a not-nullconstraint from NO INHERIT to INHERIT and vice versa, but we includedthe SET noise word in the syntax for it. The SET turns out not to benecessary and goes against what the SQL standard says for other ALTERTABLE subcommands, so remove it.This changes the way this command is processed for constraint typesother than not-null, so there are some error message changes.Reviewed-by: Peter Eisentraut <peter@eisentraut.org>Reviewed-by: Suraj Kharage <suraj.kharage@enterprisedb.com>Discussion:https://postgr.es/m/202503251602.vsxaehsyaoac@alvherre.pgsql
1 parent72c2f36 commit4a02af8

File tree

6 files changed

+43
-55
lines changed

6 files changed

+43
-55
lines changed

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
5959
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
6060
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
6161
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
62-
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>SET[ INHERIT | NO INHERIT ]
62+
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ INHERIT | NO INHERIT ]
6363
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
6464
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
6565
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -564,8 +564,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
564564
</varlistentry>
565565

566566
<varlistentry id="sql-altertable-desc-alter-constraint-inherit">
567-
<term><literal>ALTER CONSTRAINT ...SETINHERIT</literal></term>
568-
<term><literal>ALTER CONSTRAINT ...SETNO INHERIT</literal></term>
567+
<term><literal>ALTER CONSTRAINT ... INHERIT</literal></term>
568+
<term><literal>ALTER CONSTRAINT ... NO INHERIT</literal></term>
569569
<listitem>
570570
<para>
571571
These forms modify a inheritable constraint so that it becomes not

‎src/backend/commands/tablecmds.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12199,7 +12199,7 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon,
1219912199
colName = get_attname(currcon->conrelid, colNum, false);
1220012200

1220112201
/*
12202-
* Propagate the change to children. ForSET NO INHERIT, we don't
12202+
* Propagate the change to children. Forthis subcommand type we don't
1220312203
* recursively affect children, just the immediate level.
1220412204
*/
1220512205
children = find_inheritance_children(RelationGetRelid(rel),

‎src/backend/parser/gram.y

Lines changed: 8 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -2662,15 +2662,19 @@ alter_table_cmd:
26622662
n->subtype = AT_AlterConstraint;
26632663
n->def = (Node *) c;
26642664
c->conname =$3;
2665-
c->alterDeferrability =true;
2665+
if ($4 & (CAS_DEFERRABLE | CAS_NOT_DEFERRABLE |
2666+
CAS_INITIALLY_DEFERRED | CAS_INITIALLY_IMMEDIATE))
2667+
c->alterDeferrability =true;
2668+
if ($4 & CAS_NO_INHERIT)
2669+
c->alterInheritability =true;
26662670
processCASbits($4, @4,"FOREIGN KEY",
26672671
&c->deferrable,
26682672
&c->initdeferred,
2669-
NULL,NULL,NULL, yyscanner);
2673+
NULL,NULL,&c->noinherit, yyscanner);
26702674
$$ = (Node *) n;
26712675
}
2672-
/* ALTER TABLE <name> ALTER CONSTRAINTSETINHERIT*/
2673-
|ALTERCONSTRAINTnameSETINHERIT
2676+
/* ALTER TABLE <name> ALTER CONSTRAINT INHERIT*/
2677+
|ALTERCONSTRAINTnameINHERIT
26742678
{
26752679
AlterTableCmd *n = makeNode(AlterTableCmd);
26762680
ATAlterConstraint *c = makeNode(ATAlterConstraint);
@@ -2681,20 +2685,6 @@ alter_table_cmd:
26812685
c->alterInheritability =true;
26822686
c->noinherit =false;
26832687

2684-
$$ = (Node *) n;
2685-
}
2686-
/* ALTER TABLE <name> ALTER CONSTRAINT SET NO INHERIT*/
2687-
|ALTERCONSTRAINTnameSETNOINHERIT
2688-
{
2689-
AlterTableCmd *n = makeNode(AlterTableCmd);
2690-
ATAlterConstraint *c = makeNode(ATAlterConstraint);
2691-
2692-
n->subtype = AT_AlterConstraint;
2693-
n->def = (Node *) c;
2694-
c->conname =$3;
2695-
c->alterInheritability =true;
2696-
c->noinherit =true;
2697-
26982688
$$ = (Node *) n;
26992689
}
27002690
/* ALTER TABLE <name> VALIDATE CONSTRAINT ...*/

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

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1284,9 +1284,7 @@ ERROR: constraint declared INITIALLY DEFERRED must be DEFERRABLE
12841284
LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ...
12851285
^
12861286
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NO INHERIT;
1287-
ERROR: FOREIGN KEY constraints cannot be marked NO INHERIT
1288-
LINE 1: ...ER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NO INHERIT...
1289-
^
1287+
ERROR: constraint "fktable_fk_fkey" of relation "fktable" is not a not-null constraint
12901288
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
12911289
ERROR: FOREIGN KEY constraints cannot be marked NOT VALID
12921290
LINE 1: ...ER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;

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

Lines changed: 15 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -2754,7 +2754,7 @@ alter table inh_nn2 inherit inh_nn1;
27542754
create table inh_nn3 (f4 float) inherits (inh_nn2);
27552755
create table inh_nn4 (f5 int, f4 float, f2 text, f3 int, f1 int);
27562756
alter table inh_nn4 inherit inh_nn2, inherit inh_nn1, inherit inh_nn3;
2757-
alter table inh_nn1 alter constraint inh_nn1_f1_not_nullsetinherit;
2757+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit;
27582758
select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
27592759
from pg_constraint where contype = 'n' and
27602760
conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
@@ -2767,8 +2767,8 @@ select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinheri
27672767
inh_nn4 | inh_nn1_f1_not_null | {5} | 3 | f | f
27682768
(4 rows)
27692769

2770-
-- ALTER CONSTRAINTSETNO INHERIT should work on top-level constraints
2771-
alter table inh_nn1 alter constraint inh_nn1_f1_not_nullsetno inherit;
2770+
-- ALTER CONSTRAINT NO INHERIT should work on top-level constraints
2771+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null no inherit;
27722772
select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
27732773
from pg_constraint where contype = 'n' and
27742774
conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
@@ -2799,15 +2799,15 @@ drop table inh_nn1, inh_nn2, inh_nn3, inh_nn4;
27992799
create table inh_nn1 (f1 int not null no inherit);
28002800
create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
28012801
insert into inh_nn2 values(NULL, 'sample', 1);
2802-
alter table inh_nn1 alter constraint inh_nn1_f1_not_nullsetinherit;
2802+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit;
28032803
ERROR: column "f1" of relation "inh_nn2" contains null values
28042804
delete from inh_nn2;
28052805
create table inh_nn3 () inherits (inh_nn2);
28062806
create table inh_nn4 () inherits (inh_nn1, inh_nn2);
28072807
NOTICE: merging multiple inherited definitions of column "f1"
28082808
alter table inh_nn1-- test multicommand alter table while at it
2809-
alter constraint inh_nn1_f1_not_nullsetinherit,
2810-
alter constraint inh_nn1_f1_not_nullsetno inherit;
2809+
alter constraint inh_nn1_f1_not_null inherit,
2810+
alter constraint inh_nn1_f1_not_null no inherit;
28112811
select conrelid::regclass, conname, coninhcount, conislocal, connoinherit
28122812
from pg_constraint where contype = 'n' and
28132813
conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
@@ -2837,10 +2837,10 @@ select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinheri
28372837
(2 rows)
28382838

28392839
-- error: inh_nn3 has an incompatible NO INHERIT constraint
2840-
alter table inh_nn1 alter constraint inh_nn1_f1_not_nullsetinherit;
2840+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit;
28412841
ERROR: cannot change NO INHERIT status of NOT NULL constraint "nn3_f1" on relation "inh_nn3"
2842-
alter table inh_nn3 alter constraint nn3_f1setinherit;
2843-
alter table inh_nn1 alter constraint inh_nn1_f1_not_nullsetinherit; -- now it works
2842+
alter table inh_nn3 alter constraint nn3_f1 inherit;
2843+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit; -- now it works
28442844
select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
28452845
from pg_constraint where contype = 'n' and
28462846
conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3')
@@ -2853,21 +2853,21 @@ select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinheri
28532853
(3 rows)
28542854

28552855
drop table inh_nn1, inh_nn2, inh_nn3;
2856-
-- Negative scenarios for alter constraint ..setinherit.
2856+
-- Negative scenarios for alter constraint .. inherit.
28572857
create table inh_nn1 (f1 int check(f1 > 5) primary key references inh_nn1, f2 int not null);
28582858
-- constraints other than not-null are not supported
2859-
alter table inh_nn1 alter constraint inh_nn1_f1_checksetinherit;
2859+
alter table inh_nn1 alter constraint inh_nn1_f1_check inherit;
28602860
ERROR: constraint "inh_nn1_f1_check" of relation "inh_nn1" is not a not-null constraint
2861-
alter table inh_nn1 alter constraint inh_nn1_pkeysetinherit;
2861+
alter table inh_nn1 alter constraint inh_nn1_pkey inherit;
28622862
ERROR: constraint "inh_nn1_pkey" of relation "inh_nn1" is not a not-null constraint
2863-
alter table inh_nn1 alter constraint inh_nn1_f1_fkeysetinherit;
2863+
alter table inh_nn1 alter constraint inh_nn1_f1_fkey inherit;
28642864
ERROR: constraint "inh_nn1_f1_fkey" of relation "inh_nn1" is not a not-null constraint
28652865
-- try to drop a nonexistant constraint
2866-
alter table inh_nn1 alter constraint foosetinherit;
2866+
alter table inh_nn1 alter constraint foo inherit;
28672867
ERROR: constraint "foo" of relation "inh_nn1" does not exist
28682868
-- Can't modify inheritability of inherited constraints
28692869
create table inh_nn2 () inherits (inh_nn1);
2870-
alter table inh_nn2 alter constraint inh_nn1_f2_not_nullsetno inherit;
2870+
alter table inh_nn2 alter constraint inh_nn1_f2_not_null no inherit;
28712871
ERROR: cannot alter inherited constraint "inh_nn1_f2_not_null" on relation "inh_nn2"
28722872
drop table inh_nn1, inh_nn2;
28732873
--

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

Lines changed: 15 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1099,13 +1099,13 @@ alter table inh_nn2 inherit inh_nn1;
10991099
createtableinh_nn3 (f4 float) inherits (inh_nn2);
11001100
createtableinh_nn4 (f5int, f4 float, f2text, f3int, f1int);
11011101
altertable inh_nn4 inherit inh_nn2, inherit inh_nn1, inherit inh_nn3;
1102-
altertable inh_nn1 alterconstraint inh_nn1_f1_not_nullsetinherit;
1102+
altertable inh_nn1 alterconstraint inh_nn1_f1_not_null inherit;
11031103
select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
11041104
from pg_constraintwhere contype='n'and
11051105
conrelid::regclass::textin ('inh_nn1','inh_nn2','inh_nn3','inh_nn4')
11061106
order by2,1;
1107-
-- ALTER CONSTRAINTSETNO INHERIT should work on top-level constraints
1108-
altertable inh_nn1 alterconstraint inh_nn1_f1_not_nullsetno inherit;
1107+
-- ALTER CONSTRAINT NO INHERIT should work on top-level constraints
1108+
altertable inh_nn1 alterconstraint inh_nn1_f1_not_null no inherit;
11091109
select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
11101110
from pg_constraintwhere contype='n'and
11111111
conrelid::regclass::textin ('inh_nn1','inh_nn2','inh_nn3','inh_nn4')
@@ -1122,13 +1122,13 @@ drop table inh_nn1, inh_nn2, inh_nn3, inh_nn4;
11221122
createtableinh_nn1 (f1intnot null no inherit);
11231123
createtableinh_nn2 (f2text, f3int) inherits (inh_nn1);
11241124
insert into inh_nn2values(NULL,'sample',1);
1125-
altertable inh_nn1 alterconstraint inh_nn1_f1_not_nullsetinherit;
1125+
altertable inh_nn1 alterconstraint inh_nn1_f1_not_null inherit;
11261126
deletefrom inh_nn2;
11271127
createtableinh_nn3 () inherits (inh_nn2);
11281128
createtableinh_nn4 () inherits (inh_nn1, inh_nn2);
11291129
altertable inh_nn1-- test multicommand alter table while at it
1130-
alterconstraint inh_nn1_f1_not_nullsetinherit,
1131-
alterconstraint inh_nn1_f1_not_nullsetno inherit;
1130+
alterconstraint inh_nn1_f1_not_null inherit,
1131+
alterconstraint inh_nn1_f1_not_null no inherit;
11321132
select conrelid::regclass, conname, coninhcount, conislocal, connoinherit
11331133
from pg_constraintwhere contype='n'and
11341134
conrelid::regclass::textin ('inh_nn1','inh_nn2','inh_nn3','inh_nn4')
@@ -1144,26 +1144,26 @@ select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinheri
11441144
conrelid::regclass::textin ('inh_nn1','inh_nn2','inh_nn3')
11451145
order by2,1;
11461146
-- error: inh_nn3 has an incompatible NO INHERIT constraint
1147-
altertable inh_nn1 alterconstraint inh_nn1_f1_not_nullsetinherit;
1148-
altertable inh_nn3 alterconstraint nn3_f1setinherit;
1149-
altertable inh_nn1 alterconstraint inh_nn1_f1_not_nullsetinherit;-- now it works
1147+
altertable inh_nn1 alterconstraint inh_nn1_f1_not_null inherit;
1148+
altertable inh_nn3 alterconstraint nn3_f1 inherit;
1149+
altertable inh_nn1 alterconstraint inh_nn1_f1_not_null inherit;-- now it works
11501150
select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
11511151
from pg_constraintwhere contype='n'and
11521152
conrelid::regclass::textin ('inh_nn1','inh_nn2','inh_nn3')
11531153
order by2,1;
11541154
droptable inh_nn1, inh_nn2, inh_nn3;
11551155

1156-
-- Negative scenarios for alter constraint ..setinherit.
1156+
-- Negative scenarios for alter constraint .. inherit.
11571157
createtableinh_nn1 (f1intcheck(f1>5)primary keyreferences inh_nn1, f2intnot null);
11581158
-- constraints other than not-null are not supported
1159-
altertable inh_nn1 alterconstraint inh_nn1_f1_checksetinherit;
1160-
altertable inh_nn1 alterconstraint inh_nn1_pkeysetinherit;
1161-
altertable inh_nn1 alterconstraint inh_nn1_f1_fkeysetinherit;
1159+
altertable inh_nn1 alterconstraint inh_nn1_f1_check inherit;
1160+
altertable inh_nn1 alterconstraint inh_nn1_pkey inherit;
1161+
altertable inh_nn1 alterconstraint inh_nn1_f1_fkey inherit;
11621162
-- try to drop a nonexistant constraint
1163-
altertable inh_nn1 alterconstraint foosetinherit;
1163+
altertable inh_nn1 alterconstraint foo inherit;
11641164
-- Can't modify inheritability of inherited constraints
11651165
createtableinh_nn2 () inherits (inh_nn1);
1166-
altertable inh_nn2 alterconstraint inh_nn1_f2_not_nullsetno inherit;
1166+
altertable inh_nn2 alterconstraint inh_nn1_f2_not_null no inherit;
11671167

11681168
droptable inh_nn1, inh_nn2;
11691169

0 commit comments

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