Date: Tue, 14 Mar 2023 12:54:35 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: James Robertson <james@jsrobertson.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: multicolumn partitioning help
Message-ID: <ZBC029JYqZFbsIHH@telsasoft.com>
References: 
 <CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZBC029JYqZFbsIHH%40telsasoft.com>
Precedence: bulk

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