Skip to content

Commit 4a02af8

Browse files
alvherrealvherre
committed
Simplify syntax for ALTER TABLE ALTER CONSTRAINT NO INHERIT
Commit d45597f introduced the ability to change a not-null constraint from NO INHERIT to INHERIT and vice versa, but we included the SET noise word in the syntax for it. The SET turns out not to be necessary and goes against what the SQL standard says for other ALTER TABLE subcommands, so remove it. This changes the way this command is processed for constraint types other 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 parent 72c2f36 commit 4a02af8

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 ... SET INHERIT</literal></term>
568-
<term><literal>ALTER CONSTRAINT ... SET NO 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. For SET NO INHERIT, we don't
12202+
* Propagate the change to children. For this 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 CONSTRAINT SET INHERIT */
2673-
| ALTER CONSTRAINT name SET INHERIT
2676+
/* ALTER TABLE <name> ALTER CONSTRAINT INHERIT */
2677+
| ALTER CONSTRAINT name INHERIT
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-
| ALTER CONSTRAINT name SET NO INHERIT
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_null set inherit;
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 CONSTRAINT SET NO INHERIT should work on top-level constraints
2771-
alter table inh_nn1 alter constraint inh_nn1_f1_not_null set no 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_null set inherit;
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_null set inherit,
2810-
alter constraint inh_nn1_f1_not_null set no 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_null set inherit;
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_f1 set inherit;
2843-
alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit; -- 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 .. set inherit.
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_check set inherit;
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_pkey set inherit;
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_fkey set inherit;
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 foo set inherit;
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_null set no 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
create table inh_nn3 (f4 float) inherits (inh_nn2);
11001100
create table inh_nn4 (f5 int, f4 float, f2 text, f3 int, f1 int);
11011101
alter table inh_nn4 inherit inh_nn2, inherit inh_nn1, inherit inh_nn3;
1102-
alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit;
1102+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit;
11031103
select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
11041104
from pg_constraint where contype = 'n' and
11051105
conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
11061106
order by 2, 1;
1107-
-- ALTER CONSTRAINT SET NO INHERIT should work on top-level constraints
1108-
alter table inh_nn1 alter constraint inh_nn1_f1_not_null set no inherit;
1107+
-- ALTER CONSTRAINT NO INHERIT should work on top-level constraints
1108+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null no inherit;
11091109
select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
11101110
from pg_constraint where contype = 'n' and
11111111
conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
@@ -1122,13 +1122,13 @@ drop table inh_nn1, inh_nn2, inh_nn3, inh_nn4;
11221122
create table inh_nn1 (f1 int not null no inherit);
11231123
create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
11241124
insert into inh_nn2 values(NULL, 'sample', 1);
1125-
alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit;
1125+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit;
11261126
delete from inh_nn2;
11271127
create table inh_nn3 () inherits (inh_nn2);
11281128
create table inh_nn4 () inherits (inh_nn1, inh_nn2);
11291129
alter table inh_nn1 -- test multicommand alter table while at it
1130-
alter constraint inh_nn1_f1_not_null set inherit,
1131-
alter constraint inh_nn1_f1_not_null set no inherit;
1130+
alter constraint inh_nn1_f1_not_null inherit,
1131+
alter constraint inh_nn1_f1_not_null no inherit;
11321132
select conrelid::regclass, conname, coninhcount, conislocal, connoinherit
11331133
from pg_constraint where contype = 'n' and
11341134
conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
@@ -1144,26 +1144,26 @@ select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinheri
11441144
conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3')
11451145
order by 2, 1;
11461146
-- error: inh_nn3 has an incompatible NO INHERIT constraint
1147-
alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit;
1148-
alter table inh_nn3 alter constraint nn3_f1 set inherit;
1149-
alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit; -- now it works
1147+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit;
1148+
alter table inh_nn3 alter constraint nn3_f1 inherit;
1149+
alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit; -- now it works
11501150
select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
11511151
from pg_constraint where contype = 'n' and
11521152
conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3')
11531153
order by 2, 1;
11541154
drop table inh_nn1, inh_nn2, inh_nn3;
11551155

1156-
-- Negative scenarios for alter constraint .. set inherit.
1156+
-- Negative scenarios for alter constraint .. inherit.
11571157
create table inh_nn1 (f1 int check(f1 > 5) primary key references inh_nn1, f2 int not null);
11581158
-- constraints other than not-null are not supported
1159-
alter table inh_nn1 alter constraint inh_nn1_f1_check set inherit;
1160-
alter table inh_nn1 alter constraint inh_nn1_pkey set inherit;
1161-
alter table inh_nn1 alter constraint inh_nn1_f1_fkey set inherit;
1159+
alter table inh_nn1 alter constraint inh_nn1_f1_check inherit;
1160+
alter table inh_nn1 alter constraint inh_nn1_pkey inherit;
1161+
alter table inh_nn1 alter constraint inh_nn1_f1_fkey inherit;
11621162
-- try to drop a nonexistant constraint
1163-
alter table inh_nn1 alter constraint foo set inherit;
1163+
alter table inh_nn1 alter constraint foo inherit;
11641164
-- Can't modify inheritability of inherited constraints
11651165
create table inh_nn2 () inherits (inh_nn1);
1166-
alter table inh_nn2 alter constraint inh_nn1_f2_not_null set no inherit;
1166+
alter table inh_nn2 alter constraint inh_nn1_f2_not_null no inherit;
11671167

11681168
drop table inh_nn1, inh_nn2;
11691169

0 commit comments

Comments
 (0)