Re: pg_dump emits ALTER TABLE ONLY partitioned_table

On Sun, Apr 9, 2017 at 10:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> While I admit that I've not been paying close attention to the whole
> table partitioning business, I wonder whether we have any clearly written
> down specification about (a) how much partition member tables are allowed
> to deviate schema-wise from their parent, and (b) how DDL semantics on
> partitioned tables differ from DDL semantics for traditional inheritance.
> Obviously those are closely related questions. But the fact that this
> bug exists at all shows that there's been some lack of clarity on (b),
> and so I wonder whether we have any clarity on (a) either.

Children can have constraints (including NOT NULL constraints) which
parents lack, and can have a different column order, but must have
exactly the same column names and types.

The point here is, of course, not that there's any real value in the
parent columns being (a, b) and the child columns being (b, a),
although we do allow that, but rather than somebody might have a
parent with (a, b) and a child that has those plus a dropped column.
Explaining to a user - to whom the dropped column is invisible - why
that child couldn't be attached as a partition of that parent would be
difficult, so it seemed best (to me, anyway, and I think to other
people who were paying attention) to rule that the partitioning code
has to cope with the possibility of attribute numbers varying across
partitions. (Also consider the reverse case, where the parent has a
dropped column and the prospective child doesn't have one with the
same width in the same location.)

In Amit's example from the original post, the child has an implicit
NOT NULL constraint that does not exist in the parent. p1.b isn't
declared NOT NULL, but the fact that it is range-partitioned on b
requires it to be so, just as we would do if b were declared as the
PRIMARY KEY. Somehow that's not playing nice with pg_dump, but I'm
still fuzzy on the details.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company