+-- a PK in parent must have a not-null in child that it can mark inherited
+create table inh_parent (a int primary key);
+create table inh_child (a int primary key);
+alter table inh_child inherit inh_parent; -- nope
+alter table inh_child alter a set not null;
+alter table inh_child inherit inh_parent; -- now it works
+ERROR: relation "inh_parent" would be inherited from more than once
in src/test/regress/sql/inherit.sql, the comments at the end of the
command, seem to conflict with the output?
-------------------------------------------------------------------------------
ALTER TABLE ALTER COLUMN SET NOT NULL
implicitly means
ALTER TABLE ALTER COLUMN SET NOT NULL NO INHERIT.
So in ATExecSetNotNull
if (conForm->connoinherit && recurse)
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot change NO INHERIT status of NOT
NULL constraint \"%s\" on relation \"%s\"",
NameStr(conForm->conname),
RelationGetRelationName(rel)));
should be
if (conForm->connoinherit)
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot change NO INHERIT status of NOT
NULL constraint \"%s\" on relation \"%s\"",
NameStr(conForm->conname),
RelationGetRelationName(rel)));
then we can avoid the weird case like below:
drop table if exists pp1;
create table pp1 (f1 int not null no inherit);
ALTER TABLE pp1 ALTER f1 SET NOT NULL;
ALTER TABLE ONLY pp1 ALTER f1 SET NOT NULL;
-------------------------------------------------------------------------------
+ else if (rel->rd_rel->relhassubclass &&
+ find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("not-null constraint on column \"%s\" must be removed in
child tables too",
+ colName),
+ errhint("Do not specify the ONLY keyword."));
+ }
this part in ATExecDropNotNull is not necessary?
per alter_table.sql
<<<<<<---------->>>>>>
-- make sure we can drop a constraint on the parent but it remains on the child
CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL));
CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT
"test_drop_constr_parent_c_check";
<<<<<<---------->>>>>>
by the same way, we can drop a not-null constraint ONLY on the parent,
but it remains on the child.
if we not remove the above part then
ALTER TABLE ONLY DROP CONSTRAINT
will behave differently from
ALTER TABLE ONLY ALTER COLUMN DROP NOT NULL.
example:
drop table pp1,cc1, cc2;
create table pp1 (f1 int not null);
create table cc1 (f2 text, f3 int) inherits (pp1);
create table cc2(f4 float) inherits(pp1,cc1);
alter table only pp1 drop constraint pp1_f1_not_null; --works.
alter table only pp1 alter column f1 drop not null; --- error, should also work.
-------------------------------------------------------------------------------
