Re: pg_dump emits ALTER TABLE ONLY partitioned_table

On 2017/04/13 6:22, Robert Haas wrote:
> On Wed, Apr 12, 2017 at 3:29 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> 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.
>
> So I think I was indeed confused before, and I think you're basically
> right here, but on one point I think you are not right -- ALTER TABLE
> ONLY .. CHECK () doesn't work on a table with inheritance children
> regardless of whether the children already have the matching
> constraint:
>
> rhaas=# create table foo (a int, b text);
> CREATE TABLE
> rhaas=# create table bar () inherits (foo);
> CREATE TABLE
> rhaas=# alter table only foo add check (a = 1);
> ERROR: constraint must be added to child tables too
> rhaas=# alter table only bar add check (a = 1);
> ALTER TABLE
> rhaas=# alter table only foo add check (a = 1);
> ERROR: constraint must be added to child tables too
>
> It looks like ALTER TABLE ONLY works find on a table with no children,
> but once it's got children it no longer works, period.

By the way, there is a workaround with traditional inheritance:

alter table only foo add constraint chka check (a > 0) no inherit;
ALTER TABLE

But we don't allow NO INHERIT constraints on partitioned tables, so we
will get an error with them anyway.

alter table only parted_parent add constraint chka check (a > 0) no inherit;
ERROR: cannot add NO INHERIT constraint to partitioned table "parted_parent"

> However,
> you're right that you can add the constraint to the as-yet-childless
> table and then future children will inherit the constraint properly.
> Continuing the previous example:
>
> rhaas=# drop table bar;
> DROP TABLE
> rhaas=# alter table only foo add check (a = 1);
> ALTER TABLE
> rhaas=# create table bar () inherits (foo);
> CREATE TABLE
>
> So, regarding Amit's 0001:
>
> - I think we should update the relevant hunk of the documentation
> rather than just removing it.

OK, I agree. I tweaked the existing bullet point about differences from
traditional inheritance when using ONLY with partitioned tables.

> - Should we similarly allow TRUNCATE ONLY foo and ALTER TABLE ONLY foo
> .. to work on a partitioned table without partitions, or is that just
> pointless tinkering? That seems to be the only case where, after this
> patch, an ONLY operation will fail on a childless partitioned table.

I fixed TRUNCATE ONLY to not complain when no partitions exist. Patch
already takes care of the ALTER TABLE ONLY cases.

Updated patches attached (0002 and 0003 unchanged).

Thanks,
Amit