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.
