Re: pg_dump emits ALTER TABLE ONLY partitioned_table

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. 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.

- 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.

Do you want to be responsible for committing these or should I do it?

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