Re: [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

It is a patch for the matter which I reported before.

When a column is inherited from multiple relations, ALTER TABLE with
RENAME TO option is problematic.
This patch fixes the matter. In correctly, it prevent to rename columns
inherited from multiple relations and merged.

Also see the past discussion:
http://archives.postgresql.org/pgsql-hackers/2009-11/msg00138.php

postgres=# CREATE TABLE t1 (a int, b int);
CREATE TABLE
postgres=# CREATE TABLE t2 (b int, c int);
CREATE TABLE
postgres=# CREATE TABLE t3 (x text) inherits (t1, t2);
NOTICE: merging multiple inherited definitions of column "b"
CREATE TABLE
postgres=# SELECT * FROM t3;
a | b | c | x
---+---+---+---
(0 rows)

It looks to me fine.

postgres=# ALTER TABLE t1 RENAME b TO y;
ALTER TABLE
postgres=# SELECT * FROM t3;
a | y | c | x
---+---+---+---
(0 rows)

postgres=# SELECT * FROM t1;
a | y
---+---
(0 rows)

It looks to me fine.

postgres=# SELECT * FROM t2;
ERROR: could not find inherited attribute "b" of relation "t3"

Oops, when we refer the t3 via t2, it expects the inherited relation
also has the column "b", but it was already renamed.

One trouble is regression test. The misc_create test create a_star
table, then it is inherited by b_star and c_star, then these are
inherited to d_star table. Then misc test rename the a_star.a, but
this patch prevent it.

It looks like works well, but it is a corner case, because d_star.a
is eventually inherited from a_star via b_star and c_star, and these
are all the inherited relations.
In generally, we don't have reasonable way to rename all the related
columns upper and lower of the inheritance relationship.

Thanks,

(2009/11/05 9:57), KaiGai Kohei wrote:
> Tom Lane wrote:
>> Thom Brown<thombrown(at)gmail(dot)com> writes:
>>> 2009/11/4 Alvaro Herrera<alvherre(at)commandprompt(dot)com>:
>>>> KaiGai Kohei wrote:
>>>>> I think we should not allow to rename a column with attinhcount> 1.
>>
>>>> I think we should fix ALTER TABLE to cope with multiple inheritance.
>>
>>> I'd be interested to see how this should work.
>>
>> Yeah. I don't think a "fix" is possible, because there is no
>> non-astonishing way for it to behave. I think KaiGai is right that
>> forbidding the rename is the best solution.
>
> The attached patch forbids rename when the attribute is inherited
> from multiple parents.
>
> postgres=# CREATE TABLE t1 (a int, b int);
> CREATE TABLE
> postgres=# CREATE TABLE t2 (b int, c int);
> CREATE TABLE
> postgres=# CREATE TABLE t3 (d int) INHERITS (t1, t2);
> NOTICE: merging multiple inherited definitions of column "b"
> CREATE TABLE
> postgres=# SELECT * FROM t3;
> a | b | c | d
> ---+---+---+---
> (0 rows)
>
> postgres=# ALTER TABLE t1 RENAME b TO x;
> ERROR: cannot rename multiple inherited column "b"
>
>
> The regression test detected a matter in the misc test.
>
> It tries to rename column "a" of "a_star" table, but it failed due to
> the new restriction.
>
> --
> -- test the "star" operators a bit more thoroughly -- this time,
> -- throw in lots of NULL fields...
> --
> -- a is the type root
> -- b and c inherit from a (one-level single inheritance)
> -- d inherits from b and c (two-level multiple inheritance)
> -- e inherits from c (two-level single inheritance)
> -- f inherits from e (three-level single inheritance)
> --
> CREATE TABLE a_star (
> class char,
> a int4
> );
>
> CREATE TABLE b_star (
> b text
> ) INHERITS (a_star);
>
> CREATE TABLE c_star (
> c name
> ) INHERITS (a_star);
>
> CREATE TABLE d_star (
> d float8
> ) INHERITS (b_star, c_star);
>
> At the misc test,
>
> --- 242,278 ----
> ALTER TABLE c_star* RENAME COLUMN c TO cc;
> ALTER TABLE b_star* RENAME COLUMN b TO bb;
> ALTER TABLE a_star* RENAME COLUMN a TO aa;
> + ERROR: cannot rename multiple inherited column "a"
> SELECT class, aa
> FROM a_star* x
> WHERE aa ISNULL;
> ! ERROR: column "aa" does not exist
> ! LINE 1: SELECT class, aa
> !
>
> It seems to me it is a case the regression test to be fixed up.
> (We don't have any reasonable way to know whether a certain attribute
> has a same source, or not.)
>
> Any comments?
>
>
>
>

--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>