Re: pg_dump problem with dropped NOT NULL on child table

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).

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
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346