Re: Declarative partitioning - another take
| От | Rajkumar Raghuwanshi |
|---|---|
| Тема | Re: Declarative partitioning - another take |
| Дата | |
| Msg-id | CAKcux6=SAaBEdbyZ10Lu_gvXWvKk78Lm9LiX-0SYyjUCj7eBug@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Declarative partitioning - another take (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
| Ответы |
Re: Declarative partitioning - another take
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
| Список | pgsql-hackers |
On Wed, Sep 7, 2016 at 3:58 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
Thanks for clarifying, But I could see same problem of moving data when adding a new non-default partition with unbounded range partition.
For example give here, Initially I have create a partition table test with test_p3 as unbounded end,
Later tried to change test_p3 to contain 7-9 values only, and adding a new partition test_p4 contain 10-unbound.
--create partition table and some leafs
CREATE TABLE test (a int, b int) PARTITION BY RANGE(a);
CREATE TABLE test_p1 PARTITION OF test FOR VALUES START (1) END (4);
CREATE TABLE test_p2 PARTITION OF test FOR VALUES START (4) END (7);
CREATE TABLE test_p3 PARTITION OF test FOR VALUES START (7) END UNBOUNDED;
--insert some data
INSERT INTO test SELECT i, i*10 FROM generate_series(1,3) i;
INSERT INTO test SELECT i, i*10 FROM generate_series(4,6) i;
INSERT INTO test SELECT i, i*10 FROM generate_series(7,13) i;
--directly not able to attach test_p4 because of overlap error, hence detached test_p3 and than attaching test_p4
SELECT tableoid::regclass,* FROM test;
tableoid | a | b
----------+----+-----
test_p1 | 1 | 10
test_p1 | 2 | 20
test_p1 | 3 | 30
test_p2 | 4 | 40
test_p2 | 5 | 50
test_p2 | 6 | 60
test_p3 | 7 | 70
test_p3 | 8 | 80
test_p3 | 9 | 90
test_p3 | 10 | 100
test_p3 | 11 | 110
test_p3 | 12 | 120
test_p3 | 13 | 130
(13 rows)
ALTER TABLE test DETACH PARTITION test_p3;
CREATE TABLE test_p4 (like test);
ALTER TABLE test ATTACH PARTITION test_p4 FOR VALUES start (10) end UNBOUNDED;
--now can not attach test_p3 because of overlap with test_p4, causing data loss from main test table.
ALTER TABLE test ATTACH PARTITION test_p3 FOR VALUES start (7) end (10);
ERROR: source table contains a row violating partition bound specification
ALTER TABLE test ATTACH PARTITION test_p3 FOR VALUES start (7) end (13);
ERROR: partition "test_p3" would overlap partition "test_p4"
SELECT tableoid::regclass,* FROM test;
tableoid | a | b
----------+---+----
test_p1 | 1 | 10
test_p1 | 2 | 20
test_p1 | 3 | 30
test_p2 | 4 | 40
test_p2 | 5 | 50
test_p2 | 6 | 60
(6 rows)
Hi,
On 2016/09/07 17:56, Rajkumar Raghuwanshi wrote:
> Hi,
>
> I have a query regarding list partitioning,
>
> For example if I want to store employee data in a table, with "IT" dept
> employee in emp_p1 partition, "HR" dept employee in emp_p2 partition and if
> employee belongs to other than these two, should come in emp_p3 partition.
>
> In this case not sure how to create partition table. Do we have something
> like we have UNBOUNDED for range partition or oracle have "DEFAULT" for
> list partition.
>
> create table employee (empid int, dept varchar) partition by list(dept);
> create table emp_p1 partition of employee for values in ('IT');
> create table emp_p2 partition of employee for values in ('HR');
> create table emp_p3 partition of employee for values in (??);
Sorry, no such feature is currently offered. It might be possible to
offer something like a "default" list partition which accepts values other
than those specified for other existing partitions. However, that means
if we add a non-default list partition after a default one has been
created, the implementation must make sure that it moves any values from
the default partition that now belong to the newly created partition.
Thanks,
Amit
Thanks for clarifying, But I could see same problem of moving data when adding a new non-default partition with unbounded range partition.
For example give here, Initially I have create a partition table test with test_p3 as unbounded end,
Later tried to change test_p3 to contain 7-9 values only, and adding a new partition test_p4 contain 10-unbound.
--create partition table and some leafs
CREATE TABLE test (a int, b int) PARTITION BY RANGE(a);
CREATE TABLE test_p1 PARTITION OF test FOR VALUES START (1) END (4);
CREATE TABLE test_p2 PARTITION OF test FOR VALUES START (4) END (7);
CREATE TABLE test_p3 PARTITION OF test FOR VALUES START (7) END UNBOUNDED;
--insert some data
INSERT INTO test SELECT i, i*10 FROM generate_series(1,3) i;
INSERT INTO test SELECT i, i*10 FROM generate_series(4,6) i;
INSERT INTO test SELECT i, i*10 FROM generate_series(7,13) i;
--directly not able to attach test_p4 because of overlap error, hence detached test_p3 and than attaching test_p4
SELECT tableoid::regclass,* FROM test;
tableoid | a | b
----------+----+-----
test_p1 | 1 | 10
test_p1 | 2 | 20
test_p1 | 3 | 30
test_p2 | 4 | 40
test_p2 | 5 | 50
test_p2 | 6 | 60
test_p3 | 7 | 70
test_p3 | 8 | 80
test_p3 | 9 | 90
test_p3 | 10 | 100
test_p3 | 11 | 110
test_p3 | 12 | 120
test_p3 | 13 | 130
(13 rows)
ALTER TABLE test DETACH PARTITION test_p3;
CREATE TABLE test_p4 (like test);
ALTER TABLE test ATTACH PARTITION test_p4 FOR VALUES start (10) end UNBOUNDED;
--now can not attach test_p3 because of overlap with test_p4, causing data loss from main test table.
ALTER TABLE test ATTACH PARTITION test_p3 FOR VALUES start (7) end (10);
ERROR: source table contains a row violating partition bound specification
ALTER TABLE test ATTACH PARTITION test_p3 FOR VALUES start (7) end (13);
ERROR: partition "test_p3" would overlap partition "test_p4"
SELECT tableoid::regclass,* FROM test;
tableoid | a | b
----------+---+----
test_p1 | 1 | 10
test_p1 | 2 | 20
test_p1 | 3 | 30
test_p2 | 4 | 40
test_p2 | 5 | 50
test_p2 | 6 | 60
(6 rows)
В списке pgsql-hackers по дате отправления:
Следующее
От: Magnus HaganderДата:
Сообщение: Re: Useless dependency assumption libxml2 -> libxslt in MSVC scripts