Thread: Re: [BUGS] BUG #13779: Inherited check constraint becomes non-inherited when related column is changed
Re: [BUGS] BUG #13779: Inherited check constraint becomes non-inherited when related column is changed
From
Tom Lane
Date:
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