Re: pg_dump emits ALTER TABLE ONLY partitioned_table

Amit,

* Amit Langote (Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp) wrote:
> On 2017/04/11 0:26, Robert Haas wrote:
> > 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.
>
> Also, what is different in the partitioned parent case is that NOT NULL
> constraints must be inherited. That is, one cannot add it only to the parent.

If I'm following, children can have additional constraints but any
constraints on the parent must also exist on all the children. Is that
correct?

> --
> -- partitioning inheritance
> --
> create table parted_parent (a int) partition by list (a);
> create table part partition of parted_parent for values in (1);
>
> -- this is same as traditional inheritance
> alter table only parted_parent add constraint chka check (a > 0);
> -- ERROR: constraint must be added to child tables too

Ok, this makes sense, but surely that constraint does, in fact, exist on
the child already or we wouldn't be trying to dump out this constraint
that exists on the parent?

> > 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.
>
> Actually, I would like to change the problem definition from "ALTER TABLE
> ONLY partitioned_table should be avoided" to "Emitting partition's
> attributes separately should be avoided".

I don't follow why we think doing:

CREATE TABLE t1 (c1 int);
ALTER TABLE ONLY t1 SET c1 NOT NULL;

is really different from:

CREATE TABLE t1 (c1 int NOT NULL);

or why we should teach pg_dump that it's "correct" to consider those two
to be different. There are specific cases where they have to be done
independently, but that's for views because we don't have a way to set a
default on a view column during CREATE VIEW, or to deal with dropped
columns or traditionally inheirited columns.

What isn't clear to me is why the CREATE TABLE + ALTER TABLE isn't
working, when apparently a CREATE TABLE with the NOT NULL included would
work. The issue here seems like it's the order in which the operations
are happening in, and not that CREATE TABLE + ALTER TABLE is somehow
different than just the CREATE TABLE.

> create table p (a int, b int) partition by list (a);
> create table p1 partition of p for values in (1) partition by range (b);
> create table p11 partition of p1 (
> a not null default '1'
> ) for values from (1) to (10);

Using the above example, doing a pg_dump and then a restore (into a
clean initdb'd cluster), I get the following:

=# CREATE TABLE p (
-# a integer,
-# b integer
-# )
-# PARTITION BY LIST (a);
CREATE TABLE

=*# CREATE TABLE p1 PARTITION OF p
-*# FOR VALUES IN (1)
-*# PARTITION BY RANGE (b);
CREATE TABLE

=*# ALTER TABLE ONLY p1 ALTER COLUMN b SET NOT NULL;
ERROR: constraint must be added to child tables too

Now, perhaps I'm confused, but isn't p1 the child here? Which is
supposed to be able to have constraints that the parent doesn't?

We haven't even gotten to the point where p1 is a parent yet because p11
hasn't been created yet. Further, according to psql's \d, 'p1.b'
already has a NOT NULL constraint on it, so the above really should just
be a no-op.

I get the feeling that we're looking in the wrong place for the issue
here.

Thanks!

Stephen