The following bug has been logged online:
Bug reference: 5582
Logged by: Henk Enting
Email address: h.d.ent...@mgrid.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;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
