Re: pg_dump emits ALTER TABLE ONLY partitioned_table

Robert,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Apr 12, 2017 at 6:29 AM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> > Actually, p1 is a partitioned table, so the error. And I realize that
> > that's a wrong behavior. Currently the check is performed using only the
> > relkind, which is bogus. Specifying ONLY should cause an error only when
> > the table has partitions.
>
> That sounds like a REALLY bad idea, because now you're going to end up
> with a constraint that can never be enforced against any actual data
> rows ... or else you're going to later pretend that ONLY wasn't
> specified. I think the rule that partitioned tables can't have
> non-inherited constraints is absolutely right, and relaxing it is
> quite wrong.

I'm not following what you're getting at here.

There's already a constraint on the table, and ALTER TABLE ONLY doesn't
say anything about what happens later on (certainly it doesn't make new
tables created with 'LIKE' have bits omitted, if that's what you were
thinking). Lastly, the error being thrown certainly seems to imply that
one needs to go fix all the child tables to have the constraint first
and then the constraint can be added to the parent (presumably using the
same ALTER TABLE ONLY command). If there aren't any child tables, then
it should work, if there *are* child tables and they've got the
necessary constraint, then this should be allowed, so that future child
tables create will have the constraint.

> I think you had the right idea upthread when you suggested dumping it this way:
>
> CREATE TABLE p1 PARTITION OF p (
> b NOT NULL
> )
> FOR VALUES IN (1)
> PARTITION BY RANGE (b);
>
> That looks absolutely right to me, and very much principled.

It's not principled at all to claim that CREATE TABLE + NOT NULL is
somehow different from CREATE TABLE + ALTER TABLE SET NOT NULL and that
one should work and the other shouldn't.

Thanks!

Stephen