Re: pg_dump problem with dropped NOT NULL on child table

On 01/14/2016 12:59 AM, Karsten Hilbert wrote:
> On Wed, Jan 13, 2016 at 12:10:15PM -0800, Adrian Klaver wrote:
>
>> On 01/13/2016 11:38 AM, Karsten Hilbert wrote:
>
>>> create table parent (
>>> not_null_in_parent integer not null
>>> );
>>>
>>> create table child() inherits (parent);
>>> alter table child
>>> alter column not_null_in_parent
>>> drop not null
>>> ;
>>>
>>> Is this a bug or am I doing things I shouldn't hope work ?
>>
>> The latter if I am following the below correctly:
>>
>> http://www.postgresql.org/docs/9.5/static/ddl-inherit.html
>>
>> "All check constraints and not-null constraints on a parent table are
>> automatically inherited by its children. Other types of constraints (unique,
>> primary key, and foreign key constraints) are not inherited."
>
> Hello Adrian, thanks for chipping in. I am aware of the above
> paragraph. In fact, it made me choose the inheritance
> approach to the problem at hand in the first place :-)
>
> Note though that, usually, inheriting is a one-time act --
> such as during child table creation. What stays behind is the
> legacy - which can be changed (DROP NOT NULL).
>
> I was, then, surprised by the fact that the pg_dump /
> pg_restore cycle did not "faithfully" reproduce the child
> table. That made me question my ways.
>
> Maybe I shouldn't have been surprised because PG inheritance
> doesn't end at table creation time (child and parent are
> still linked through data even in the future).

Actually more than that:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

INHERITS ( parent_table [, ... ] )

Use of INHERITS creates a persistent relationship between the new child
table and its parent table(s). Schema modifications to the parent(s)
normally propagate to children as well, and by default the data of the
child table is included in scans of the parent(s).

>
> Meatspace inheritance is more like
>
> CREATE TABLE pseudo_child_table AS SELECT FROM pseudo_parent_table ...
>
> While PG inheritance is a bit more like view-on-steroids.
>
> Thanks,
> Karsten
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com