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

Commitf6a0acb

Browse files
alvherrehoriguti
authored andcommitted
Disallow changing NO INHERIT status of a not-null constraint
It makes no sense to add a NO INHERIT not-null constraint to a childtable that already has one in that column inherited from its parent.Disallow that, and add tests for the relevant cases.Per complaint from Kyotaro Horiguchi. I also used part of his proposedpatch.Co-authored-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com>Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org>Discussion:https://postgr.es/m/20230828.161658.1184657435220765047.horikyota.ntt@gmail.com
1 parentcf5f7e8 commitf6a0acb

File tree

5 files changed

+47
-6
lines changed

5 files changed

+47
-6
lines changed

‎src/backend/catalog/heap.c‎

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2533,7 +2533,7 @@ AddRelationNewConstraints(Relation rel,
25332533
* update its catalog status and we're done.
25342534
*/
25352535
if (AdjustNotNullInheritance1(RelationGetRelid(rel),colnum,
2536-
cdef->inhcount))
2536+
cdef->inhcount,cdef->is_no_inherit))
25372537
continue;
25382538

25392539
/*
@@ -2830,6 +2830,17 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
28302830

28312831
if (old->attnum==attnum)
28322832
{
2833+
/*
2834+
* If we get a constraint from the parent, having a local NO
2835+
* INHERIT one doesn't work.
2836+
*/
2837+
if (constr->is_no_inherit)
2838+
ereport(ERROR,
2839+
(errcode(ERRCODE_DATATYPE_MISMATCH),
2840+
errmsg("cannot define not-null constraint on column \"%s\" with NO INHERIT",
2841+
strVal(linitial(constr->keys))),
2842+
errdetail("The column has an inherited not-null constraint.")));
2843+
28332844
inhcount++;
28342845
old_notnulls=foreach_delete_current(old_notnulls,lc2);
28352846
}

‎src/backend/catalog/pg_constraint.c‎

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -669,7 +669,8 @@ extractNotNullColumn(HeapTuple constrTup)
669669
* If no not-null constraint is found for the column, return false.
670670
*/
671671
bool
672-
AdjustNotNullInheritance1(Oidrelid,AttrNumberattnum,intcount)
672+
AdjustNotNullInheritance1(Oidrelid,AttrNumberattnum,intcount,
673+
boolis_no_inherit)
673674
{
674675
HeapTupletup;
675676

@@ -681,6 +682,19 @@ AdjustNotNullInheritance1(Oid relid, AttrNumber attnum, int count)
681682

682683
pg_constraint=table_open(ConstraintRelationId,RowExclusiveLock);
683684
conform= (Form_pg_constraint)GETSTRUCT(tup);
685+
686+
/*
687+
* Don't let the NO INHERIT status change (but don't complain
688+
* unnecessarily.) In the future it might be useful to let an
689+
* inheritable constraint replace a non-inheritable one, but we'd need
690+
* to recurse to children to get it added there.
691+
*/
692+
if (is_no_inherit!=conform->connoinherit)
693+
ereport(ERROR,
694+
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
695+
errmsg("cannot change NO INHERIT status of inherited NOT NULL constraint \"%s\" on relation \"%s\"",
696+
NameStr(conform->conname),get_rel_name(relid)));
697+
684698
if (count>0)
685699
conform->coninhcount+=count;
686700

@@ -691,9 +705,9 @@ AdjustNotNullInheritance1(Oid relid, AttrNumber attnum, int count)
691705
get_rel_name(relid));
692706

693707
/*
694-
* If theconstraints are no longer inherited, markthem local. It's
695-
* arguable that we should dropthem instead, but it's hard to see
696-
*thatbeing better. The user can drop it manually later.
708+
* If theconstraint is no longer inherited, markit local. It's
709+
* arguable that we should dropit instead, but it's hard to see that
710+
* being better. The user can drop it manually later.
697711
*/
698712
if (conform->coninhcount==0)
699713
conform->conislocal= true;

‎src/include/catalog/pg_constraint.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -248,7 +248,8 @@ extern char *ChooseConstraintName(const char *name1, const char *name2,
248248
externHeapTuplefindNotNullConstraintAttnum(Oidrelid,AttrNumberattnum);
249249
externHeapTuplefindNotNullConstraint(Oidrelid,constchar*colname);
250250
externAttrNumberextractNotNullColumn(HeapTupleconstrTup);
251-
externboolAdjustNotNullInheritance1(Oidrelid,AttrNumberattnum,intcount);
251+
externboolAdjustNotNullInheritance1(Oidrelid,AttrNumberattnum,intcount,
252+
boolis_no_inherit);
252253
externvoidAdjustNotNullInheritance(Oidrelid,Bitmapset*columns,intcount);
253254
externList*RelationGetNotNullConstraints(Oidrelid,boolcooked);
254255

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2051,6 +2051,15 @@ Not-null constraints:
20512051
Inherits: pp1,
20522052
cc1
20532053

2054+
-- cannot create table with inconsistent NO INHERIT constraint
2055+
create table cc3 (a2 int not null no inherit) inherits (cc1);
2056+
NOTICE: moving and merging column "a2" with inherited definition
2057+
DETAIL: User-specified column moved to the position of the inherited column.
2058+
ERROR: cannot define not-null constraint on column "a2" with NO INHERIT
2059+
DETAIL: The column has an inherited not-null constraint.
2060+
-- change NO INHERIT status of inherited constraint: no dice, it's inherited
2061+
alter table cc2 add not null a2 no inherit;
2062+
ERROR: cannot change NO INHERIT status of inherited NOT NULL constraint "nn" on relation "cc2"
20542063
-- remove constraint from cc2: no dice, it's inherited
20552064
alter table cc2 alter column a2 drop not null;
20562065
ERROR: cannot drop inherited constraint "nn" of relation "cc2"

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

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -736,6 +736,12 @@ alter table pp1 alter column f1 set not null;
736736
\d+ cc1
737737
\d+ cc2
738738

739+
-- cannot create table with inconsistent NO INHERIT constraint
740+
createtablecc3 (a2intnot null no inherit) inherits (cc1);
741+
742+
-- change NO INHERIT status of inherited constraint: no dice, it's inherited
743+
altertable cc2 addnot null a2 no inherit;
744+
739745
-- remove constraint from cc2: no dice, it's inherited
740746
altertable cc2 alter column a2 dropnot null;
741747

0 commit comments

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