Hi,
We ran into a problem on 9.0beta3 with check constraints using table
inheritance in a multi-level hierarchy with multiple inheritance.
A test script is provided below and a proposed patch is attached to this
email.
Regards,
Henk Enting, Yeb Havinga
MGRID B.V.
http://www.mgrid.net
/*
First, create a local inheritance structure:
level_0_parent
level_0_child inherits level_0_parent
This structure is the base level. The table definition and also check
constraints are defined on this level.
Add two levels that inherit this structure:
level_1_parent inherits level_0_parent
level_1_child inherits level_1_parent, level_0_child
level_2_parent inherits level_1_parent
level_2_child inherits level_2_parent, level_1_child
BTW: there is a reason that we want e.g. level_1_child to inherit from
both level_1_parent and level_0_child:
we want the data of level_1_child to be visible in both level_0_child
and level_1_parent
*/
DROP SCHEMA IF EXISTS test_inheritance CASCADE;
CREATE SCHEMA test_inheritance;
SET search_path TO test_inheritance;
CREATE TABLE level_0_parent (i int);
CREATE TABLE level_0_child (a text) INHERITS (level_0_parent);
CREATE TABLE level_1_parent() INHERITS (level_0_parent);
CREATE TABLE level_1_child() INHERITS (level_0_child, level_1_parent);
CREATE TABLE level_2_parent() INHERITS (level_1_parent);
CREATE TABLE level_2_child() INHERITS (level_1_child, level_2_parent);
-- Now add a check constraint on the top level table:
ALTER TABLE level_0_parent ADD CONSTRAINT a_check_constraint CHECK (i IN
(0,1));
/*
Check the "coninhcount" attribute of pg_constraint
Doxygen says this about the parameter:
coninhcount: Number of times inherited from direct parent relation(s)
On our machine (running 9.0beta3) the query below returns a
coninhcount of 3 for the level_2_child table.
This doesn't seem correct because the table only has two direct
parents.
*/
SELECT t.oid, t.relname, c.coninhcount
FROM pg_class t
JOIN pg_constraint c ON (c.conrelid = t.oid)
JOIN pg_namespace n ON (t.relnamespace = n.oid)
WHERE n.nspname = 'test_inheritance'
ORDER BY t.oid;
-- Next, drop the constraint on the top level table
ALTER TABLE level_0_parent DROP CONSTRAINT a_check_constraint;
/*
The constraint should now be dropped from all the tables in the
hierarchy, but the constraint hasn't been dropped on the level_2_child
table. It is still there and has a coninhcount of 1.
*/
SELECT t.oid, t.relname, c.conname, c.coninhcount
FROM pg_class t
JOIN pg_constraint c ON (c.conrelid = t.oid)
JOIN pg_namespace n ON (t.relnamespace = n.oid)
WHERE n.nspname = 'test_inheritance'
ORDER BY t.oid;
/*
Trying to drop this constraint that shouldn't be there anymore won't work.
The "drop constraint" statement below returns:
ERROR: cannot drop inherited constraint "a_check_constraint" of
relation "level_2_child"
NB after fixing this bug, the statement should return
"constraint does not exist"
*/
ALTER TABLE level_2_child DROP CONSTRAINT a_check_constraint;
