On Wed, Apr 12, 2017 at 3:29 PM, Stephen Frost <sfr...@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. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
