Re: Attached partition not considering altered column properties ofroot partition. - Mailing list pgsql-hackers

Hi Prabhat,

On Tue, Jul 2, 2019 at 5:12 PM Prabhat Sahu
<prabhat.sahu@enterprisedb.com> wrote:
>
> Hi,
>
> In below testcase when I changed the staorage option for root partition, newly attached partition not including the
changedstaorage 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