Re: Attached partition not considering altered column properties of root partition.

Hi Prabhat,

On Tue, Jul 2, 2019 at 5:12 PM Prabhat Sahu
<prabhat(dot)sahu(at)enterprisedb(dot)com> wrote:
>
> Hi,
>
> In below testcase when I changed the staorage option for root partition, newly attached partition not including the changed staorage option.
> Is this an expected behavior?

Thanks for the report. This seems like a bug. Documentation claims
that the child tables inherit column storage options from the parent
table. That's actually enforced in only some cases.

1. If you create the child table as a child to begin with (that is,
not attach it as child after the fact):

create table parent (a text);
create table child () inherits (parent);
select attrelid::regclass, attname, attstorage from pg_attribute where
attrelid in ('parent'::regclass, 'child'::regclass) and attname = 'a';
attrelid │ attname │ attstorage
──────────┼─────────┼────────────
parent │ a │ x
child │ a │ x
(2 rows)

2. If you change the parent's column's storage option, child's column
is recursively changed.

alter table parent alter a set storage main;
select attrelid::regclass, attname, attstorage from pg_attribute where
attrelid in ('parent'::regclass, 'child'::regclass) and attname = 'a';
attrelid │ attname │ attstorage
──────────┼─────────┼────────────
parent │ a │ m
child │ a │ m
(2 rows)

However, we fail to enforce the rule when the child is attached after the fact:

create table child2 (a text);
alter table child2 inherit parent;
select attrelid::regclass, attname, attstorage from pg_attribute where
attrelid in ('parent'::regclass, 'child'::regclass,
'child2'::regclass) and attname = 'a';
attrelid │ attname │ attstorage
──────────┼─────────┼────────────
parent │ a │ m
child │ a │ m
child2 │ a │ x
(3 rows)

To fix this, MergeAttributesIntoExisting() should check that the
attribute options of a child don't conflict with the parent, which the
attached patch implements. Note that partitioning uses the same code
as inheritance, so the fix applies to it too. After the patch:

create table p (a int, b text) partition by list (a);
create table p1 partition of p for values in (1);
select attrelid::regclass, attname, attstorage from pg_attribute where
attrelid in ('p'::regclass, 'p1'::regclass) and attname = 'b';
attrelid │ attname │ attstorage
──────────┼─────────┼────────────
p │ b │ x
p1 │ b │ x
(2 rows)

alter table p alter b set storage main;
select attrelid::regclass, attname, attstorage from pg_attribute where
attrelid in ('p'::regclass, 'p1'::regclass) and attname = 'b';
attrelid │ attname │ attstorage
──────────┼─────────┼────────────
p │ b │ m
p1 │ b │ m
(2 rows)

create table p2 (like p);
select attrelid::regclass, attname, attstorage from pg_attribute where
attrelid in ('p'::regclass, 'p1'::regclass, 'p2'::regclass) and
attname = 'b';
attrelid │ attname │ attstorage
──────────┼─────────┼────────────
p │ b │ m
p1 │ b │ m
p2 │ b │ x
(3 rows)

alter table p attach partition p2 for values in (2);
ERROR: child table "p2" has different storage option for column "b" than parent
DETAIL: EXTENDED versus MAIN

-- ok after changing p2 to match
alter table p2 alter b set storage main;
alter table p attach partition p2 for values in (2);

Thanks,
Amit