Re: patch for check constraints using multiple inheritance - Mailing list pgsql-hackers

Robert Haas wrote:
> On Mon, Aug 2, 2010 at 2:56 PM, Yeb Havinga <yebhavinga@gmail.com> wrote:
>> Hence the ATOneLevelRecursion routing is usable in its
>> current form for all callers during the prep stage, and not only
>> ATPrepAddColumn.
>
> Well, only if they happen to want the "visit each table only once"
> behavior, which might not be true for every command type.
It is actually "visit each table only once for each distinct parent". 
Looking at the command types for ALTER TABLE, I see none where this 
behaviour would be incorrect.

That put aside, the top1/top2 example is interesting in the sense that 
it reveals other problems besides the wrong attinhcount at the basement. 
For an example see the script below. The underlying cause is the failure 
of the code to recognize that if relation C inherits from both A and B, 
where A and B both have column x, that A.x 'is the same as' B.x, where 
the 'is the same as' relation is the same that holds for (A.x, C.x) and 
(B.x, C.x), which the code does a lot of trouble for to recognize. This 
means that if some definition is altered on A.x, only C.x is updated and 
B.x not touched. IMO this is wrong and either a multiple inheritance 
structure like this should be prohibited, since the user did not 
explicitly declare that A.x and B.x 'are the same' (by e.g. defining a 
relation D.x and have A and B inherit from that), or the code should 
update parents of relations when the childs are updated.

The difficulty is in exactly specifying the 'is the same' as relation, 
i.e. under what conditions are columns A.x and B.x allowed to be merged 
to C.x. In the regression test there's only a small amount of tests, but 
one of them shows that the 'is the same' as relation does not mean 
everything is the same, as it shows that default values may differ.

regards,
Yeb Havinga


DROP SCHEMA IF EXISTS test_inheritance CASCADE;
CREATE SCHEMA test_inheritance;
SET search_path TO test_inheritance;

CREATE TABLE top1 (i int);
CREATE TABLE top2 (i int);
CREATE TABLE bottom () INHERITS (top1, top2);
CREATE TABLE basement () INHERITS (bottom);

ALTER TABLE top1 ADD COLUMN a_table_column INTEGER;
ALTER TABLE top2 ADD COLUMN a_table_column INTEGER;

SELECT t.oid, t.relname, a.attinhcount, a.attname
FROM pg_class t
JOIN pg_attribute a ON (a.attrelid = t.oid)
JOIN pg_namespace n ON (t.relnamespace = n.oid)
WHERE n.nspname = 'test_inheritance' AND a.attname LIKE '%table_column%'
ORDER BY oid;

ALTER TABLE top1 RENAME COLUMN a_table_column TO another_table_column;

SELECT t.oid, t.relname, a.attinhcount, a.attname
FROM pg_class t
JOIN pg_attribute a ON (a.attrelid = t.oid)
JOIN pg_namespace n ON (t.relnamespace = n.oid)
WHERE n.nspname = 'test_inheritance' AND a.attname LIKE '%table_column%'
ORDER BY oid;

ALTER TABLE top2 RENAME COLUMN a_table_column TO another_table_column;