Overlapping values (?) in multi-column partitioned tables

From: Christophe Pettus <xof(at)thebuild(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Overlapping values (?) in multi-column partitioned tables
Date: 2024-09-10 22:13:47
Message-ID: 2FAC43EB-7E04-476A-BEBE-39CDBCA0EDCC@thebuild.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I am clearly not understanding something. Consider:

> xof=# create table t (pk bigint not null, ts timestamp not null) partition by range (ts, pk);
> CREATE TABLE
> xof=# create table t1 partition of t for values from ('2024-01-01'::timestamp, minvalue) to ('2024-02-01'::timestamp, maxvalue);
> CREATE TABLE
> xof=# 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=#

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 <= 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.)

I'm obviously missing something, but... what? Thank you!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brent Wood 2024-09-10 22:23:28 Re: Overlapping values (?) in multi-column partitioned tables
Previous Message Achilleas Mantzios 2024-09-10 17:41:32 Re: Strange permission effect depending on DEFERRABILITY