Thanks Amit for the fix patch,
I have applied the patch and verified the issue.
The attached partition with altered column properties shows error as below:
postgres=# alter table p attach partition p2 for values in (2);
psql: ERROR: child table "p2" has different storage option for column "b"
than parent
DETAIL: EXTENDED versus MAIN
Thanks,
Prabhat Sahu
On Wed, Jul 3, 2019 at 7:23 AM Amit Langote <amitlangot...@gmail.com> wrote:
> Hi Prabhat,
>
> On Tue, Jul 2, 2019 at 5:12 PM Prabhat Sahu
> <prabhat.s...@enterprisedb.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
