Обсуждение: Multiple inheritance and ALTER TABLE issue

Hi everyone,

Consider the test case below:

CREATE TABLE t0(c0 boolean);
CREATE TABLE t1(c0 boolean);
CREATE TABLE t2(c0 boolean) INHERITS(t0, t1);
ALTER TABLE t0 ALTER c0 TYPE TEXT;
UPDATE t1 SET c0 = TRUE; -- ERROR:  attribute "c0" of relation "t2"
does not match parent's type

The ALTER TABLE leaves t1 behind in an unusable state, which is
somewhat unexpected. I would expect that either the ALTER TABLE fails
unless also t1 is explicitly updated, or that the ALTER TABLE updates
the column in t1. Updating the other tables does not cause a problem:

UPDATE t2 SET c0 = TRUE; -- no error
UPDATE t0 SET c0 = 'asdf'; -- no error

Is this behavior intended? The documentation mentions that ALTER TABLE
"will propagate any changes in column data definitions and check
constraints down the inheritance hierarchy", but not up the
inheritance hierarchy, so this might be expected.

Best,
Manuel

Manuel Rigger <rigger.manuel@gmail.com> writes:
> Consider the test case below:

> CREATE TABLE t0(c0 boolean);
> CREATE TABLE t1(c0 boolean);
> CREATE TABLE t2(c0 boolean) INHERITS(t0, t1);
> ALTER TABLE t0 ALTER c0 TYPE TEXT;
> UPDATE t1 SET c0 = TRUE; -- ERROR:  attribute "c0" of relation "t2"
> does not match parent's type

> The ALTER TABLE leaves t1 behind in an unusable state, which is
> somewhat unexpected. I would expect that either the ALTER TABLE fails
> unless also t1 is explicitly updated, or that the ALTER TABLE updates
> the column in t1. Updating the other tables does not cause a problem:

> UPDATE t2 SET c0 = TRUE; -- no error
> UPDATE t0 SET c0 = 'asdf'; -- no error

> Is this behavior intended?

Hm.  I would say that the ALTER COLUMN TYPE operation should have thrown
an error instead of trying to change the type of a multiply-inherited
column.  As you say, no good can come of that.

Given such a restriction, there would be no way to change c0's type while
the multi-inheritance situation exists.  You'd have to disinherit t2 from
one parent or the other, change the type in both parents, and then
re-inherit from the removed parent.  That is possible, and given the lack
of prior complaints, it seems like it'd be a sufficient answer to anyone
who needs to do it.

            regards, tom lane

I wrote:
> Manuel Rigger <rigger.manuel@gmail.com> writes:
>> Consider the test case below:

>> CREATE TABLE t0(c0 boolean);
>> CREATE TABLE t1(c0 boolean);
>> CREATE TABLE t2(c0 boolean) INHERITS(t0, t1);
>> ALTER TABLE t0 ALTER c0 TYPE TEXT;
>> UPDATE t1 SET c0 = TRUE; -- ERROR:  attribute "c0" of relation "t2"
>> does not match parent's type

>> Is this behavior intended?

> Hm.  I would say that the ALTER COLUMN TYPE operation should have thrown
> an error instead of trying to change the type of a multiply-inherited
> column.  As you say, no good can come of that.

I've pushed a fix along that line.  Thanks for the report!

            regards, tom lane