Обсуждение: PG11 Hash partitioning and null values in the partition key

Поиск
Список
Период
Сортировка

PG11 Hash partitioning and null values in the partition key

От
Daniel Westermann
Дата:
Hi,

given this setup:

create table part2 ( a int, list varchar(10) ) partition by hash (a);
create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);

insert into part2 (a,list) values (1,'aaa');
insert into part2 (a,list) values (2,'bbb');
insert into part2 (a,list) values (3,'ccc');

... it is possible to insert rows like this which will always go to the first partition:

insert into part2 (a,list) values (null,'ddd');
insert into part2 (a,list) values (null,'eee');
select * from part2_1;
 a | list
---+------
 2 | bbb
   | ddd
   | eee
(3 rows)

I suppose this is intended but I could not find anything in the documentation about that. Can someone please clarify the logic behind that?

Thanks in advance
Daniel

Re: PG11 Hash partitioning and null values in the partition key

От
amul sul
Дата:

On Fri, Jul 13, 2018, 7:35 PM Daniel Westermann <daniel.westermann@dbi-services.com> wrote:
Hi,

given this setup:

create table part2 ( a int, list varchar(10) ) partition by hash (a);
create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);

insert into part2 (a,list) values (1,'aaa');
insert into part2 (a,list) values (2,'bbb');
insert into part2 (a,list) values (3,'ccc');

... it is possible to insert rows like this which will always go to the first partition:

insert into part2 (a,list) values (null,'ddd');
insert into part2 (a,list) values (null,'eee');
select * from part2_1;
 a | list
---+------
 2 | bbb
   | ddd
   | eee
(3 rows)

I suppose this is intended but I could not find anything in the documentation about that. Can someone please clarify the logic behind that?

The calculated hash value for the null value will be zero, therefore, it will fall to the partition having remainder zero.

​Regards,
Amul​

Re: PG11 Hash partitioning and null values in the partition key

От
Daniel Westermann
Дата:
Hi,

given this setup:

create table part2 ( a int, list varchar(10) ) partition by hash (a);
create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);

insert into part2 (a,list) values (1,'aaa');
insert into part2 (a,list) values (2,'bbb');
insert into part2 (a,list) values (3,'ccc');

... it is possible to insert rows like this which will always go to the first partition:

insert into part2 (a,list) values (null,'ddd');
insert into part2 (a,list) values (null,'eee');
select * from part2_1;
 a | list
---+------
 2 | bbb
   | ddd
   | eee
(3 rows)

I suppose this is intended but I could not find anything in the documentation about that. Can someone please clarify the logic behind that?

>> The calculated hash value for the null value will be zero, therefore, it will fall to the partition having remainder zero.


Thank you, Amul