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
attstorage-inherit-bug.patch
Description: Binary data
