Re: multicolumn partitioning help

[Laurenz Albe]

On Sun, 2023-03-12 at 13:59 -0400, James Robertson wrote:
> I am having issues with multicolumn partitioning. For reference I am using 
> the following link as my guide:
> https://www.postgresql.org/docs/devel/sql-createtable.html
> 
> To demonstrate my problem, I created a simple table called humans. I want to 
> partition by the year
> of the human birth and then the first character of the hash. So for each year 
> I'll have year*16 partitions. (hex)
> 
> CREATE TABLE humans (
>     hash bytea,
>     fname text,
>     dob date
>     )PARTITION BY RANGE (EXTRACT(YEAR FROM dob),substring(hash::text, 1, 1));
>     
> Reading the documentation:   "When creating a range partition, the lower 
> bound specified with
> FROM is an inclusive bound, whereas the upper bound specified with TO is an 
> exclusive bound".
> 
> However I can't insert any of the following after the first one, because it 
> says it overlaps.
> Do I need to do anything different when defining multi-column partitions?
> 
> 
> This works:
> CREATE TABLE humans_1968_0 PARTITION OF humans FOR VALUES FROM (1968, '0') TO 
> (1969, '1');
> 
> 
> These fail: 
> CREATE TABLE humans_1968_1 PARTITION OF humans FOR VALUES FROM (1968, '1') TO 
> (1969, '2');

Justin has explained what the problem is, let me supply a solution.

I think you want subpartitioning, like

  CREATE TABLE humans (
 hash bytea,
 fname text,
 dob date
  ) PARTITION BY LIST (EXTRACT (YEAR FROM dob));

  CREATE TABLE humans_2002
 PARTITION OF humans FOR VALUES IN (2002)
 PARTITION BY HASH (hash);

  CREATE TABLE humans_2002_0
 PARTITION OF humans_2002 FOR VALUES WITH (MODULUS 26, REMAINDER 0);

  [...]

  CREATE TABLE humans_2002_25
 PARTITION OF humans_2002 FOR VALUES WITH (MODULUS 26, REMAINDER 25);

and so on for the other years.

Yours,
Laurenz Albe

Re: multicolumn partitioning help

[Justin Pryzby]

On Sun, Mar 12, 2023 at 01:59:32PM -0400, James Robertson wrote:
> Hey folks,
> I am having issues with multicolumn partitioning. For reference I am using
> the following link as my guide:
> https://www.postgresql.org/docs/devel/sql-createtable.html
> 
> Reading the documentation:   "When creating a range partition, the lower
> bound specified with FROM is an inclusive bound, whereas the upper bound
> specified with TO is an exclusive bound".
> 
> However I can't insert any of the following after the first one, because it
> says it overlaps. Do I need to do anything different when defining
> multi-column partitions?

The bounds are compared like rows:

When creating a range partition, the lower bound specified with FROM is
an inclusive bound, whereas the upper bound specified with TO is an
exclusive bound. That is, the values specified in the FROM list are
valid values of the corresponding partition key columns for this
partition, whereas those in the TO list are not. Note that this
statement must be understood according to the rules of row-wise
comparison (Section 9.24.5). For example, given PARTITION BY RANGE
(x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2,
x=2 with any non-null y, and x=3 with any y<4.

https://www.postgresql.org/docs/current/functions-comparisons.html#ROW-WISE-COMPARISON

> This works:
> CREATE TABLE humans_1968_0 PARTITION OF humans FOR VALUES FROM (1968, '0')
> TO (1969, '1');

This table is everything from 1968 (starting with '0') to 1969

> These fail:
> CREATE TABLE humans_1968_1 PARTITION OF humans FOR VALUES FROM (1968, '1')
> TO (1969, '2');

Which is why these are overlapping.

> CREATE TABLE humans_1969_1 PARTITION OF humans FOR VALUES FROM (1969, '1')
> TO (1970, '2');

This one doesn't fail, but it "occupies" / subjugates all of 1969
starting with 1.

-- 
Justin

multicolumn partitioning help

[James Robertson]

Hey folks,
I am having issues with multicolumn partitioning. For reference I am using
the following link as my guide:
https://www.postgresql.org/docs/devel/sql-createtable.html

To demonstrate my problem, I created a simple table called humans. I want
to partition by the year of the human birth and then the first character of
the hash. So for each year I'll have year*16 partitions. (hex)

CREATE TABLE humans (
hash bytea,
fname text,
dob date
)PARTITION BY RANGE (EXTRACT(YEAR FROM dob),substring(hash::text, 1,
1));

Reading the documentation:   "When creating a range partition, the lower
bound specified with FROM is an inclusive bound, whereas the upper bound
specified with TO is an exclusive bound".

However I can't insert any of the following after the first one, because it
says it overlaps. Do I need to do anything different when defining
multi-column partitions?


This works:
CREATE TABLE humans_1968_0 PARTITION OF humans FOR VALUES FROM (1968, '0')
TO (1969, '1');


These fail:
CREATE TABLE humans_1968_1 PARTITION OF humans FOR VALUES FROM (1968, '1')
TO (1969, '2');
CREATE TABLE humans_1968_2 PARTITION OF humans FOR VALUES FROM (1968, '2')
TO (1969, '3');
CREATE TABLE humans_1968_3 PARTITION OF humans FOR VALUES FROM (1968, '3')
TO (1969, '4');
CREATE TABLE humans_1968_4 PARTITION OF humans FOR VALUES FROM (1968, '4')
TO (1969, '5');
CREATE TABLE humans_1968_5 PARTITION OF humans FOR VALUES FROM (1968, '5')
TO (1969, '6');
CREATE TABLE humans_1968_6 PARTITION OF humans FOR VALUES FROM (1968, '6')
TO (1969, '7');
CREATE TABLE humans_1968_7 PARTITION OF humans FOR VALUES FROM (1968, '7')
TO (1969, '8');
CREATE TABLE humans_1968_8 PARTITION OF humans FOR VALUES FROM (1968, '8')
TO (1969, '9');
CREATE TABLE humans_1968_9 PARTITION OF humans FOR VALUES FROM (1968, '9')
TO (1969, 'a');
CREATE TABLE humans_1968_a PARTITION OF humans FOR VALUES FROM (1968, 'a')
TO (1969, 'b');
CREATE TABLE humans_1968_b PARTITION OF humans FOR VALUES FROM (1968, 'b')
TO (1969, 'c');
CREATE TABLE humans_1968_c PARTITION OF humans FOR VALUES FROM (1968, 'c')
TO (1969, 'd');
CREATE TABLE humans_1968_d PARTITION OF humans FOR VALUES FROM (1968, 'd')
TO (1969, 'e');
CREATE TABLE humans_1968_e PARTITION OF humans FOR VALUES FROM (1968, 'e')
TO (1969, 'f');
CREATE TABLE humans_1968_f PARTITION OF humans FOR VALUES FROM (1968, 'f')
TO (1969, 'g');
CREATE TABLE humans_1969_0 PARTITION OF humans FOR VALUES FROM (1969, '0')
TO (1970, '1');
CREATE TABLE humans_1969_1 PARTITION OF humans FOR VALUES FROM (1969, '1')
TO (1970, '2');
CREATE TABLE humans_1969_2 PARTITION OF humans FOR VALUES FROM (1969, '2')
TO (1970, '3');
CREATE TABLE humans_1969_3 PARTITION OF humans FOR VALUES FROM (1969, '3')
TO (1970, '4');
CREATE TABLE humans_1969_4 PARTITION OF humans FOR VALUES FROM (1969, '4')
TO (1970, '5');
CREATE TABLE humans_1969_5 PARTITION OF humans FOR VALUES FROM (1969, '5')
TO (1970, '6');
CREATE TABLE humans_1969_6 PARTITION OF humans FOR VALUES FROM (1969, '6')
TO (1970, '7');
CREATE TABLE humans_1969_7 PARTITION OF humans FOR VALUES FROM (1969, '7')
TO (1970, '8');
CREATE TABLE humans_1969_8 PARTITION OF humans FOR VALUES FROM (1969, '8')
TO (1970, '9');
CREATE TABLE humans_1969_9 PARTITION OF humans FOR VALUES FROM (1969, '9')
TO (1970, 'a');
CREATE TABLE humans_1969_a PARTITION OF humans FOR VALUES FROM (1969, 'a')
TO (1970, 'b');
CREATE TABLE humans_1969_b PARTITION OF humans FOR VALUES FROM (1969, 'b')
TO (1970, 'c');
CREATE TABLE humans_1969_c PARTITION OF humans FOR VALUES FROM (1969, 'c')
TO (1970, 'd');
CREATE TABLE humans_1969_d PARTITION OF humans FOR VALUES FROM (1969, 'd')
TO (1970, 'e');
CREATE TABLE humans_1969_e PARTITION OF humans FOR VALUES FROM (1969, 'e')
TO (1970, 'f');
CREATE TABLE humans_1969_f PARTITION OF humans FOR VALUES FROM (1969, 'f')
TO (1970, 'g');

Thank you for reviewing this problem.