Thread: BUG #13779: Inherited check constraint becomes non-inherited when related column is changed

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