Message-ID: <c32540af9cb7a51196754f6a5d6ce920b9dd74cb.camel@cybertec.at>
Subject: Re: Overlapping values (?) in multi-column partitioned tables
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Christophe Pettus <xof@thebuild.com>, pgsql-general
	 <pgsql-general@postgresql.org>
Date: Wed, 11 Sep 2024 00:57:38 +0200
In-Reply-To: <2FAC43EB-7E04-476A-BEBE-39CDBCA0EDCC@thebuild.com>
References: <2FAC43EB-7E04-476A-BEBE-39CDBCA0EDCC@thebuild.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/c32540af9cb7a51196754f6a5d6ce920b9dd74cb.camel%40cybertec.at>
Precedence: bulk

On Tue, 2024-09-10 at 15:13 -0700, Christophe Pettus wrote:
> I am clearly not understanding something.=C2=A0 Consider:
>=20
> > xof=3D# create table t (pk bigint not null, ts timestamp not null) part=
ition by range (ts, pk);
> > CREATE TABLE
> > xof=3D# create table t1 partition of t for values from ('2024-01-01'::t=
imestamp, minvalue) to ('2024-02-01'::timestamp, maxvalue);
> > CREATE TABLE
> > xof=3D# create table t2 partition of t for values from ('2024-02-01'::t=
imestamp, minvalue) to ('2024-03-01'::timestamp, maxvalue);
> > ERROR:=C2=A0 partition "t2" would overlap partition "t1"
> > LINE 1: ...on of t for values from ('2024-02-01'::timestamp, minvalue) =
...
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^
> > xof=3D#=20
>=20
> In what way do those partitions overlap?

In this way:

SELECT ROW('2024-02-01'::timestamp, '9223372036854775807'::bigint)
    <=3D ROW('2024-02-01'::timestamp, '-9223372036854775808'::bigint);

 ?column?=20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90
 f
(1 row)

So the upper limit of the first partition is strictly bigger than the lower=
 end
of the second partition.

"record" types have the same lexicographical sorting order as ORDER BY clau=
ses.

Perhaps your confusion is that you expect the first partition to only go up=
 to
and including ('2023-12-31 23:59:59.999999', 9223372036854775806), but that=
's not
the case.  The biggest value you can store in the first partition is
('2024-02-01 00:00:00', 9223372036854775806).

You should specify the upper bound as ('2023-12-31 23:59:59.999999', MAXVAL=
UE).

Yours,
Laurenz Albe