questions about not-null constraints and inheritance

From: "K(dot) Srinath" <k(dot)srinath(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: questions about not-null constraints and inheritance
Date: 2009-04-02 16:01:08
Message-ID: f23fee3f0904020901g3c772e6fi1750636ddf135d5f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David E. Wheeler 2009-04-02 16:10:34 Re: [HACKERS] string_to_array with empty input
Previous Message K. Srinath 2009-04-02 15:59:59 global index - work in progress