Re: Multi column range partition table

On Sun, Jul 9, 2017 at 2:42 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 6 July 2017 at 22:43, Joe Conway <mail(at)joeconway(dot)com> wrote:
>> I agree we should get this right the first time and I also agree with
>> Dean's proposal, so I guess I'm a +2
>
> On 7 July 2017 at 03:21, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> +1 to releasing this syntax in PG 10.
>
> So, that's 3 votes in favour of replacing UNBOUNDED with
> MINVALUE/MAXVALUE for range partition bounds in PG 10. Not a huge
> consensus, but no objections either. Any one else have an opinion?
>
> Robert, have you been following this thread?

Uh, no. Sorry. I agree that it's a big problem that (10, UNBOUNDED)
interpreted as a maximum value means first_column <= 10 and when
interpreted as a minimum value means first_column >= 10, because those
things aren't opposites of each other. I guess the proposal here
would make (10, MAXVALUE) as a maximum value mean first_column <= 10
and as a minimum would mean first_column > 10, and contrariwise for
MINVALUE. That seems to restore the intended design principle of the
system, which is good, but...

...originally, Amit proposed to attach a postfix INCLUSIVE or
EXCLUSIVE to each bound specification, and this does feel like a bit
of a back door to the same place, kinda. A partition defined to run
from (10, MAXVALUE) TO (11, MAXVALUE) is a lot like a partition
defined to run from (10) EXCLUSIVE to (11) EXCLUSIVE. And if we
eventually decide to allow that, then what will be the difference
between a partition which starts at (10, MAXVALUE) EXCLUSIVE and one
which starts from (10, MAXVALUE) INCLUSIVE?

I haven't thought through this well enough to be sure that there's any
problem with what is being proposed, and I definitely don't have a
better solution off the top of my head, but I feel slightly nervous.

Apologies again for the slow response - will update again by Monday.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company