Обсуждение: BUG #13779: Inherited check constraint becomes non-inherited when related column is changed
BUG #13779: Inherited check constraint becomes non-inherited when related column is changed
От
jan.dirk.zijlstra@redwood.com
Дата:
The following bug has been logged on the website: Bug reference: 13779 Logged by: Jan Dirk Zijlstra Email address: jan.dirk.zijlstra@redwood.com PostgreSQL version: 9.4.5 Operating system: Linux Description: create table parenttable (a numeric, b numeric); create table childtable () inherits (parenttable); alter table parenttable add constraint parentconstraint_a check (a > 0); alter table parenttable add constraint parentconstraint_b check (b > 0); \d+ parenttable \d+ childtable select t.relname, c.conname, c.coninhcount, c.conislocal, c.connoinherit from pg_constraint c, pg_class t where c.conname like 'parentconstraint%' and c.conrelid = t.oid; alter table parenttable alter column a set data type real ; \d+ parenttable \d+ childtable select t.relname, c.conname, c.coninhcount, c.conislocal, c.connoinherit from pg_constraint c, pg_class t where c.conname like 'parentconstraint%' and c.conrelid = t.oid; drop table childtable; drop table parenttable; When executing the commands above, you'll notice that you have in the end a child table with a constraint which is not inherited anymore. This gives issues, because - you cannot drop the constraint on the parent table anymore. ( psql:tp.sql:86: ERROR: relation 23724254 has non-inherited constraint "parentconstraint_a" ) - you can drop the constraint on the child table, but than you can again not drop the constraint on the parent anymore. (psql:tp.sql:90: ERROR: constraint "parentconstraint_a" of relation "childtable" does not exist) - you cannot uninherit the child (psql:tp.sql:103: ERROR: relation 23724399 has non-inherited constraint "parentconstraint_a") To come out of this situation, I need to - drop the local constraint on the child table - drop the inheritance from the child table - drop the local constraint on the parent table - restore the inheritance on the child table - create the constraint again on the parent table.
jan.dirk.zijlstra@redwood.com writes: > [ ALTER COLUMN TYPE leaves inherited constraints in the wrong state ] Yeah. After perusing this I've become convinced that ALTER TABLE's approach to rebuilding check constraints is fundamentally misguided. Rather than using ALTER TABLE ONLY to reconstruct a check constraint separately for each child table, we should apply a regular ALTER TABLE ADD CONSTRAINT once at the parent table. Annoyingly, we already tried to fix this area once in 5ed6546c, but that was just doubling down on the wrong basic design. The problem is actually visible in the test case added by that commit, if it had occurred to us to check the inheritance status columns: regression=# select t.relname, c.conname, c.coninhcount, c.conislocal, c.connoinherit from pg_constraint c, pg_class t where c.conname like 'test_inh_check%' and c.conrelid = t.oid; relname | conname | coninhcount | conislocal | connoinherit ----------------------+------------------------+-------------+------------+--------------test_inh_check | test_inh_check_a_check| 0 | t | ftest_inh_check_child | test_inh_check_a_check | 1 | f | f (2 rows) regression=# ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric; ALTER TABLE regression=# select t.relname, c.conname, c.coninhcount, c.conislocal, c.connoinherit from pg_constraint c, pg_class t where c.conname like 'test_inh_check%' and c.conrelid = t.oid; relname | conname | coninhcount | conislocal | connoinherit ----------------------+------------------------+-------------+------------+--------------test_inh_check | test_inh_check_a_check| 0 | t | ftest_inh_check_child | test_inh_check_a_check | 0 | t | f (2 rows) Barring objections I'll go try to fix it by removing the "ONLY" and then suppressing generation of new work queue entries for inherited child constraints. regards, tom lane