Re: Attached partition not considering altered column properties ofroot partition. - Mailing list pgsql-hackers
From | Prabhat Sahu |
---|---|
Subject | Re: Attached partition not considering altered column properties ofroot partition. |
Date | |
Msg-id | CANEvxPoa9UKQms+zw=3xSqBWi0R_FL2aj9ni48SYg0mj6SJLUw@mail.gmail.com Whole thread Raw |
In response to | Re: Attached partition not considering altered column properties ofroot partition. (Amit Langote <amitlangote09@gmail.com>) |
List | pgsql-hackers |
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
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 <amitlangote09@gmail.com> wrote:
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 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
pgsql-hackers by date: