Re: Overlapping values (?) in multi-column partitioned tables

On Tue, 2024-09-10 at 15:13 -0700, Christophe Pettus wrote:
> 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 this way:

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

?column?
══════════
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 clauses.

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', MAXVALUE).

Yours,
Laurenz Albe