Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Overlapping values (?) in multi-column partitioned tables
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <SY7P300MB07614A220ADFE92147763474A19A2@SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM>
Date: Tue, 10 Sep 2024 15:30:22 -0700
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <FAB00E39-441F-4119-A714-5367F32B5603@thebuild.com>
References: <2FAC43EB-7E04-476A-BEBE-39CDBCA0EDCC@thebuild.com>
 <SY7P300MB07614A220ADFE92147763474A19A2@SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM>
To: Brent Wood <Brent.Wood@niwa.co.nz>
Archived-At: <https://www.postgresql.org/message-id/FAB00E39-441F-4119-A714-5367F32B5603%40thebuild.com>
Precedence: bulk

The comparisons for partitions are lower <=3D value < upper, so this =
works fine:

> xof=3D# create table t (pk bigint not null, ts timestamp not null) =
partition by range (ts);
> CREATE TABLE
> xof=3D# create table t1 partition of t for values from =
('2024-01-01'::timestamp) to ('2024-02-01'::timestamp);
> CREATE TABLE
> xof=3D# create table t2 partition of t for values from =
('2024-02-01'::timestamp) to ('2024-03-01'::timestamp);
> CREATE TABLE

In both cases, it's unambiguous that a record with =
('2024-02-01'::timestamp) would go into t2.

> On Sep 10, 2024, at 15:23, Brent Wood <Brent.Wood@niwa.co.nz> wrote:
>=20
> I'm not sure of the less than vs less than or equals in this one, so =
it may be my ignorance... but which partition gets records with a date =
of '24-02-01", it seems that without a more explicit definition there is =
ambiguity on dates at the partition limits when those dates are common =
to multiple partitions?
>=20
> To avoid this shouldn't the definition be:
>=20
>> xof=3D# create table t (pk bigint not null, ts timestamp not null) =
partition by range (ts, pk);
>> CREATE TABLE
>> xof=3D# create table t1 partition of t for values from =
('2024-01-01'::timestamp, minvalue) to ('2024-01-31'::timestamp, =
maxvalue);
>> CREATE TABLE
>> xof=3D# create table t2 partition of t for values from =
('2024-02-01'::timestamp, minvalue) to ('2024-02-29'::timestamp, =
maxvalue);
>=20
>=20
> Brent Wood
>=20
> Principal Technician, Fisheries
> NIWA
> DDI:  +64 (4) 3860529
> From: Christophe Pettus <xof@thebuild.com>
> Sent: Wednesday, September 11, 2024 10:13
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: Overlapping values (?) in multi-column partitioned tables
> Hi,
>=20
> I am clearly not understanding something.  Consider:
>=20
>> xof=3D# create table t (pk bigint not null, ts timestamp not null) =
partition by range (ts, pk);
>> CREATE TABLE
>> xof=3D# create table t1 partition of t for values from =
('2024-01-01'::timestamp, minvalue) to ('2024-02-01'::timestamp, =
maxvalue);
>> CREATE TABLE
>> xof=3D# create table t2 partition of t for values from =
('2024-02-01'::timestamp, minvalue) to ('2024-03-01'::timestamp, =
maxvalue);
>> ERROR:  partition "t2" would overlap partition "t1"
>> LINE 1: ...on of t for values from ('2024-02-01'::timestamp, =
minvalue) ...
>>                                                            ^
>> xof=3D#
>=20
> In what way do those partitions overlap?  In other words, there does =
not appear to be a value of (ts, pk) having '2024-01-01'::timestamp <=3D =
ts < '2024-03-01'::timestamp for any pk where it would be ambiguous =
which partition that row would go into.  (I'm imagining it on a =
cartesian plane, and there isn't any overlap between the rectangles =
those partition boundaries define.)
>=20
> I'm obviously missing something, but... what?  Thank you!
>=20
> Brent Wood=20
> Principal Technician - GIS and Spatial Data Management
> Programme Leader - Environmental Information Delivery
> +64-4-386-0529
>=20
> National Institute of Water & Atmospheric Research Ltd (NIWA)
> 301 Evans Bay Parade Hataitai Wellington New Zealand
> Connect with NIWA: niwa.co.nz Facebook LinkedIn Twitter Instagram =
YouTubeTo ensure compliance with legal requirements and to maintain =
cyber security standards, NIWA's IT systems are subject to ongoing =
monitoring, activity logging and auditing. This monitoring and auditing =
service may be provided by third parties. Such third parties can access =
information transmitted to, processed by and stored on NIWA's IT =
systems.
> Note: This email is intended solely for the use of the addressee and =
may contain information that is confidential or subject to legal =
professional privilege. If you receive this email in error please =
immediately notify the sender and delete the email.