Re: [HACKERS] 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@snowman.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers