Обсуждение: questions about not-null constraints and inheritance

Consider two tables foo(x int not null) and bar (x int).

I fire the sql
SQL1: alter table bar inherit foo;

Above sql causes "ERROR:  column "x" in child table must be marked NOT NULL".
This looks logically fine to me.

So, I fire the sql
SQL2: alter table bar alter column x set not null;

Then I make bar a child of foo by firing SQL1.

So now I have foo (x int not null), and
bar (x int not null) inherits (foo).

From this state, the questions:
-------------------------------------------
Q1. Why can I now successfully fire the following sql?
SQL3: alter table bar alter column x drop not null;
-------------------------------------------
Q2. Back to baseline, where we had not null constraints on both foo and bar.
I can remove the not null constraints by
SQL4: alter table foo alter column x drop not null;

But now, I can successfully fire
SQL5: alter table only foo alter column x set not null;

Why is this so?
-------------------------------------------
(The behavior seen in SQL3 and SQL5 seems contradictory to the
behavior seen in SQL1. Shouldn't SQL1, SQL3 and SQL5 all share the
same fate?)

From code perspective, the relevant methods are ATExecDropNotNull and
ATExecSetNotNull.

If the behavior seen above is incorrect/inconsistent, then following
changes may have to be made:
1. ATExecDropNotNull: May have to see attinhcount of x, and deduce
that not-null cannot be dropped.
2. ATExecSetNotNull: May have to always recurse; specifying ONLY
during a SET NOT NULL may have to be treated as erroneous.

Thanks,
Srinath.

"K. Srinath" <k.srinath@gmail.com> writes:
> Why is this so?

Because we don't track inheritance state for attnotnull.  There is no
way you can make it work right with marginal hacking on the ATExec
code, because there just isn't enough state.

The correct fix is probably the one alluded to in another thread today:
make NOT NULL constraints have pg_constraint entries like CHECK
constraints do.  As of 8.4 there is enough info tracked for check
constraints (cf conislocal and coninhcount) to make inheritance handle
all these corner cases.
        regards, tom lane