Re: [BUGS] BUG #13779: Inherited check constraint becomes non-inherited when related column is changed

jan(dot)dirk(dot)zijlstra(at)redwood(dot)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 | f
test_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 | f
test_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