Re: [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns - Mailing list pgsql-hackers

The similar matter can be reproduced with ALTER TABLE ... TYPE statement,
not only RENAME TO option.
 postgres=# CREATE TABLE t1 (a int); CREATE TABLE postgres=# CREATE TABLE s1 (a int); CREATE TABLE
 postgres=# CREATE TABLE ts (b int) inherits (t1, s1); NOTICE:  merging multiple inherited definitions of column "a"
CREATETABLE postgres=# ALTER TABLE t1 ALTER a TYPE text; ALTER TABLE
 
 postgres=# insert into t1 values ('aaa'); INSERT 0 1 postgres=# insert into ts values ('bbb', 2); INSERT 0 1
postgres=#SELECT * FROM t1;   a -----  aaa  bbb (2 rows)
 
 postgres=# SELECT * FROM s1; ERROR:  attribute "a" of relation "ts" does not match parent's type

In the renameatt(), we can count an expected inhcount of the column to be
renamed on find_all_inheritors() at the top-level recursion.
But it does not work well for the new one, because it is handled within
the common ATPrepCmd() scheme.

I reconsidered that we need a function to check whether the given column
is inherited from multiple root parents, or not, for each levels.
Then, it can be called from both of renameatt() and ATPrepAlterColumnType().


(2010/01/04 18:55), KaiGai Kohei wrote:
>>> The method I suggested would allow the
>>> necessary information to be extracted during the initial search for
>>> child tables, which we have to do anyway.
>>
>> find_all_inheritors() returns a clean list which does not contain
>> duplicated OID of the inherited relation, so it seems to me we need
>> to change the function prototype but it affects other parts, or to add
>> a new function which also returns number of duplications, not only OIDs.
>>
>> Or, we can call find_inheritance_children() in renameatt() as if
>> find_all_inheritors() doing except for list_concat_unique_oid().
> 
> The attached patch modified the condition to prevent renaming.
> 
> It computes an expected inhcount for each child relations on the initial
> call of the renameatt() for the parent relation.
> The find_all_inheritors_with_inhcount() returns OID of the inherited
> relations and the expected inhcoundt. If a child relation has diamond
> inheritance tree, it has its expected inhcount larger than 1.
> 
> This patch raises an error, if pg_attribute.inhcount is larger than
> the expected inhcount. It can be happen when the attribute to be
> renamed is merged from any other unrelated relations in the child
> relations.
> 
> See the example:
> 
>    postgres=# CREATE TABLE t1 (a int);
>    CREATE TABLE
>    postgres=# CREATE TABLE t2 (b int) inherits (t1);
>    CREATE TABLE
>    postgres=# CREATE TABLE t3 (c int) inherits (t1);
>    CREATE TABLE
>    postgres=# CREATE TABLE t4 (d int) inherits (t2, t3);
>    NOTICE:  merging multiple inherited definitions of column "a"
>    CREATE TABLE
> 
>    postgres=# ALTER TABLE t1 RENAME a TO x;
>    ALTER TABLE
>    postgres=# \d t4
>          Table "public.t4"
>     Column |  Type   | Modifiers
>    --------+---------+-----------
>     x      | integer |
>     b      | integer |
>     c      | integer |
>     d      | integer |
>    Inherits: t2,
>              t3
> 
> We can rename a of t1, t2, t3 and t4 correctly, although t4.a has inherited from
> multiple relations.
> 
>    postgres=# CREATE TABLE s1 (x int);
>    CREATE TABLE
>    postgres=# CREATE TABLE t5 (e int) inherits (t2, t3, s1);
>    NOTICE:  merging multiple inherited definitions of column "x"
>    NOTICE:  merging multiple inherited definitions of column "x"
>    CREATE TABLE
>    postgres=# ALTER TABLE t1 RENAME x TO y;
>    ERROR:  cannot rename multiple inherited column "x"
> 
> But, the new t5 prevent to rename "x" to "y", because t5.x is also inherited from
> the s1 and merged. So, its inhcount is 3 larger than expected inhcount (=2).
> 
>    postgres=# SELECT attname, attinhcount FROM pg_attribute where attname='x' and attrelid='t5'::regclass;
>     attname | attinhcount
>    ---------+-------------
>     x       |           3
>    (1 row)
> 
> Thanks,
> 
> 
> 
> 


-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>