public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Christophe Pettus <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Overlapping values (?) in multi-column partitioned tables
Date: Wed, 11 Sep 2024 00:57:38 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Overlapping values (?) in multi-column partitioned tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox