MIME-Version: 1.0
References: 
 <CADY9qXf_RsNNfZ88qYpzW7-3kreQq2fd+6Gezbm85pm_-FZHGQ@mail.gmail.com>
In-Reply-To: 
 <CADY9qXf_RsNNfZ88qYpzW7-3kreQq2fd+6Gezbm85pm_-FZHGQ@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 14 Apr 2026 17:19:08 +1200
Message-ID: 
 <CAApHDvqSP-E4g3ZQ0WxBbfeMbkmPhMjOm15aWXaT5+pDfO_6AA@mail.gmail.com>
Subject: Re: POC: Comparison of partitioning key values
To: John Mikk <jomikk2706@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvqSP-E4g3ZQ0WxBbfeMbkmPhMjOm15aWXaT5%2BpDfO_6AA%40mail.gmail.com>
Precedence: bulk

On Tue, 14 Apr 2026 at 09:11, John Mikk <jomikk2706@gmail.com> wrote:
> ```sql
> drop table if exists grid2 cascade;
>
> create table grid2(x bigint, y bigint) partition by range (x,y);
>
> create table g2_part1 partition of grid2 for values from (1,3) to (7,11);
> create table g2_part2 partition of grid2 for values from (7,11) to (13,15=
);
> create table g2_part3 partition of grid2 for values from (13,15) to (15,1=
7);
> create table g2_part4 partition of grid2 for values from (15,17) to (19,2=
1);
> --
> create table g2_part5 partition of grid2 for values from (5,15) to (13,17=
);
> -- [42P17] ERROR: partition "g2_part5" would overlap partition "g2_part1"
> ```
>
> Why is that?

Because (5,15) is between (1,3) (inclusive) and (7,11) (non-inclusive)

> According to the documentation, the row comparison rule for tables applie=
s (subsection 9.25.5).
> However, in the case of row comparison, it is possible to override compar=
ison operators, which is not the case when defining partitions.
> There is no way to override the comparison operator for partition ranges.=
 And is this general approach always correct in the case of partitioning?

You're free to create your own type and own btree opfamily, but I
don't see how you're going to tell it that (5,15) isn't above or equal
to (1,3), and separately, isn't below (7,11). This all works with the
notion of sorting on a single dimention, where "y" is the tiebreaker
for equal "x" values. There's just no way to map that into
2-dimentional space with table partitioning.

Note the part of the documentation in [1]:

"For example, given PARTITION BY RANGE (x,y), a partition bound FROM
(1, 2) TO (3, 4) allows x=3D1 with any y>=3D2, x=3D2 with any non-null y,
and x=3D3 with any y<4."

> Let us provide a generalized reasoning:
>
> Suppose we have a range-partitioned table with a partitioning key consist=
ing of n attributes.
> Let us consider the from and to values of the partitioning key from the f=
or values clause as points on the main diagonal of an n-dimensional paralle=
lepiped of permissible key values for a specific partition.
> Of course, our set is finite, but this perspective on the partitioning ke=
y allows us to use a fact from multidimensional geometry:
>
> Let the first parallelepiped be defined by the main diagonal points
> A =3D (a_1, a_2, ..., a_n) and A' =3D (a_1', a_2', ..., a_n'), where a_i =
< a_i' for all i,
> and the second parallelepiped be defined by the main diagonal points
> B =3D (b_1, b_2, ..., b_n) and B' =3D (b_1', b_2', ..., b_n'), where b_i =
< b_i' for all i.
>
> In this case, the following theorem (statement) holds true:

So I think you must be thinking that another RANGE column adds another
dimention. That's not the case. It's still 1 dimention, you just have
more tiebreaker columns in the notion sorting by the partition bound.

> Two parallelepipeds do not intersect if and only if there exists at least=
 one coordinate k (from 1 to n) for which the projection intervals on this =
axis do not intersect; that is, the intersection of [a_k, a_k'] and [b_k, b=
_k'] is an empty set, or equivalently, the condition (a_k' < b_k) OR (b_k' =
< a_k) holds for one of the k values.
>
> In other words, this fact establishes: first, when two figures do not int=
ersect and are separated by a hyperplane x_k =3D const; and second, the nec=
essary and sufficient condition for the figures to intersect, given that al=
l projections intersect. The latter remains valid for a parallelepiped redu=
ced to a point and can determine whether a new key belongs to a particular =
partition.
>
> The experimental patch I am proposing (v1-0001-partition-by-range.patch) =
introduces changes to the source code based on the described approach, so t=
hat the partition from the example can be created.
>
> Moreover, the algorithm for determining the partition of a new key during=
 an insert operation is even more mysterious in the current implementation;=
 my patch corrects this.

It uses a binary search to determine if the partition key columns in
the inserted tuple falls within a partition's bound. It's not clear to
me what exactly isn't correct about that.

> In the proposed patch, writing to output directly via fprintf is hardcode=
d. This allows obtaining a plain text list of existing and added ranges aft=
er each operation for the illustrative Python script.
>
> A segmentation fault will occur during update and delete operations.
>
> Or would adding an override for the range comparison operator be a more f=
lexible and correct approach?
>
> I would like to hear your opinion, dear hackers!

You can't change how RANGE partitioning works and not break things for
everyone using RANGE partitioning when they upgrade. If your patch is
proposing that, then it's going to fail. If you're proposing a new
partitioning method, then that's different. It's still a hefty amount
of work. If you're proposing that then do a detailed proposal here
before doing too much work. Remember that with declarative
partitioning, there can be only (at most) a single partition for any
given tuple. The tuple routing done during INSERT and UPDATE requires
that. Finding the correct partition must also be fast as INSERT/UPDATE
performance needs to run that code for every affected tuple.

David

[1] https://www.postgresql.org/docs/current/sql-createtable.html