Re: pg_dump - wrong order with inheritance

pinker <pinker@onet.eu> writes:
> i've just prepared test case and did some debugging. It turned out that the
> issue isn't dump order but wrong ddl generated by pg_dump.

> CREATE TABLE a00
> (
>   id   INTEGER NOT NULL,
>   name TEXT    NOT NULL,
>   CONSTRAINT a00_pkey PRIMARY KEY (id)
> )
> WITH (
> OIDS =FALSE
> );

> CREATE TABLE a03
> (
>   id   INTEGER NOT NULL,
>   name TEXT    NOT NULL,
>   CONSTRAINT a03_pkey PRIMARY KEY (id)
> )

> WITH (
> OIDS =FALSE
> );

> ALTER TABLE a03
> INHERIT a00;

> ALTER TABLE a03
> ALTER COLUMN name DROP NOT NULL;

Ah.  This is not pg_dump's fault, because actually what you've got here
is a logically inconsistent database: at this point, it's possible to
select from a00 and see some rows with null name values, which should
not be possible given the declared NOT NULL constraint on a00.name.

This is a known limitation of the backend.  It should refuse to let you
DROP NOT NULL in a case where the NOT NULL is an inherited constraint,
as it is here.  It would not let you drop a CHECK constraint in an
equivalent situation; but the NOT NULL support is older/more primitive
and currently doesn't do enough bookkeeping to realize that a03's NOT NULL
constraint is inherited from a parent table.  Fixing that is on our to-do
list, but it hasn't been very high priority.

            regards, tom lane