Thread: Bug in MergeAttributesIntoExisting() function.

Hi,

In inheritance, child column's pg_attribute.attislocal flag not getting updated, if it is inherited using ALTER TABLE
<child>INHERIT <parent>. 

Due to this, if we try to drop column(s) from parent table, which are not getting drop from child.
Attached herewith is quick patch fixing this issue.


----------------------Demonstration:
----------------------
CREATE TABLE p1 (a int , b int, c int, d int);

CREATE TABLE c1 () inherits (p1);CREATE TABLE c2 (a int , b int, c int, d int);


--Drop parent's column
ALTER TABLE p1 DROP COLUMN b;
ALTER TABLE p1 DROP COLUMN c;
ALTER TABLE p1 DROP COLUMN d;


postgres=# \d p1
      Table "public.p1"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
Number of child tables: 2 (Use \d+ to list them.)

postgres=# \d c1
      Table "public.c1"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
Inherits: p1

postgres=# \d c2
      Table "public.c2"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
 b      | integer |
 c      | integer |
 d      | integer |
Inherits: p1


----------------------
You can see columns are not dropped from child c2 table, which we have inherited using ALTER command.

Regards,
Amul Sul

Hi,

On Mon, Jan 4, 2016 at 8:11 PM, amul sul <sul_amul@yahoo.co.in> wrote:
> Hi,
>
> In inheritance, child column's pg_attribute.attislocal flag not getting updated, if it is inherited using ALTER TABLE
<child>INHERIT <parent>.
 
>
> Due to this, if we try to drop column(s) from parent table, which are not getting drop from child.
> Attached herewith is quick patch fixing this issue.
>
>
> ----------------------Demonstration:
> ----------------------
> CREATE TABLE p1 (a int , b int, c int, d int);
>
> CREATE TABLE c1 () inherits (p1);CREATE TABLE c2 (a int , b int, c int, d int);
>
>
> --Drop parent's column
> ALTER TABLE p1 DROP COLUMN b;
> ALTER TABLE p1 DROP COLUMN c;
> ALTER TABLE p1 DROP COLUMN d;
>
>
> postgres=# \d p1
>       Table "public.p1"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  a      | integer |
> Number of child tables: 2 (Use \d+ to list them.)
>
> postgres=# \d c1
>       Table "public.c1"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  a      | integer |
> Inherits: p1
>
> postgres=# \d c2
>       Table "public.c2"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  a      | integer |
>  b      | integer |
>  c      | integer |
>  d      | integer |
> Inherits: p1
>
>
> ----------------------
> You can see columns are not dropped from child c2 table, which we have inherited using ALTER command.

I'm afraid the patched behavior of MergeAttributeIntoExisting() would
be inconsistent with MergeAttributes(). For example, try with the
following:

CREATE TABLE c1(b int) INHERITS(p1);

In this case, MergeAttributes() would cause 'b' to be defined to be a
local attribute (ie, with attislocal = true) and hence would not be
dropped unlike c2 which the patched behavior would cause to be
dropped.

Am I missing something?

Thanks,
Amit

On Mon, Jan 4, 2016 at 9:28 AM, Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
> On Mon, Jan 4, 2016 at 4:41 PM, amul sul <sul_amul@yahoo.co.in> wrote:
>> Hi,
>> In inheritance, child column's pg_attribute.attislocal flag not getting
>> updated, if it is inherited using ALTER TABLE <child> INHERIT <parent>.
>>
>> Due to this, if we try to drop column(s) from parent table, which are not
>> getting drop from child.
>> Attached herewith is quick patch fixing this issue.
> Seems like you missed following command in the demonstration test:
>
> ALTER TABLE c2 INHERIT p1;
>
>>
>> You can see columns are not dropped from child c2 table, which we have
>> inherited using ALTER command.
>
> I took a quick look at this and did some testing. Patch looks good to me.
> ALTER TABLE INHERIT missing the attislocal = false check.

This is not a bug.  ALTER TABLE .. INHERIT isn't supposed to set
attislocal = false.  attislocal tracks whether there was a local copy
of the attribute definition originally - here, the answer is yes, even
after the ALTER TABLE .. INHERIT.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

amul sul <sul_amul@yahoo.co.in> writes:
> In inheritance, child column's pg_attribute.attislocal flag not getting updated, if it is inherited using ALTER TABLE
<child>INHERIT <parent>.
 

I think this patch is wrong and you have broken the intended behavior.
It's a bit hard to tell though because your example is confused.

> CREATE TABLE p1 (a int , b int, c int, d int);

> CREATE TABLE c1 () inherits (p1);CREATE TABLE c2 (a int , b int, c int, d int);

> --Drop parent's column
> ALTER TABLE p1 DROP COLUMN b;
> ALTER TABLE p1 DROP COLUMN c;
> ALTER TABLE p1 DROP COLUMN d;

> postgres=# \d p1
>       Table "public.p1"
>  Column |  Type   | Modifiers 
> --------+---------+-----------
>  a      | integer | 
> Number of child tables: 2 (Use \d+ to list them.)

Say what?  At this point only c1 is a child of p1.  I assume you've
left something out, either an ALTER INHERIT or an INHERITS clause
in CREATE TABLE c2.

Either way, however, the way you declared c2, it has an independent
local definition of all four columns, and so they should not go away
even if the parent's columns go away.  This is exactly the purpose
that attislocal was created to serve, and your patch destroys it.
        regards, tom lane

>On Monday, 4 January 2016 8:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>Either way, however, the way you declared c2, it has an independent
>local definition of all four columns, and so they should not go away
>even if the parent's columns go away.  This is exactly the purpose
>that attislocal was created to serve, and your patch destroys it.

Understood this is not a bug, thank you.


I missed ALTER TABLE statement in previous test case, correct test case is as follow:
-- create table
CREATE TABLE p1 (a int , b int);
CREATE TABLE c2 (a int , b int);

-- alter c2' inheritance 
ALTER TABLE c2 INHERIT p1;

-- drop column b
ALTER TABLE p1 DROP COLUMN b;

-- table description 


postgres=# \d p1
Table "public.p1"
Column |  Type  | Modifiers 
--------+---------+-----------
a    | integer | 
Number of child tables: 1 (Use \d+ to list them.)

postgres=# \d c2
Table "public.c2"
Column |  Type  | Modifiers 
--------+---------+-----------
a    | integer | 
b    | integer | 
Inherits: p1



Regards,
Amul Sul