BUG #5582: check constraints get merged to often with multiple inheritance

The following bug has been logged online:

Bug reference: 5582
Logged by: Henk Enting
Email address: h(dot)d(dot)enting(at)mgrid(dot)net
PostgreSQL version: 9.0beta3, 8.4.4
Operating system: linux (64 bit)
Description: check constraints get merged to often with multiple
inheritance
Details:

Check constraints in a multi-level table hierarchy using multiple
inheritance get merged too often. The result is that lower level constraints
have a too-high value of coninhcount and are not removed when the parent
table's constraint is removed.

The bug was posted yesterday in the postgresql-hackers list but it probably
belongs here.

The original post (+ a proposed patch):
http://archives.postgresql.org/pgsql-hackers/2010-07/msg01499.php

Regards,

Henk Enting

Test script with comments detailing the problem:

/*

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;