Re: [HACKERS] Re: [COMMITTERS] pgsql: Use MINVALUE/MAXVALUE insteadof UNBOUNDED for range partition b - Mailing list pgsql-hackers

On 2017/08/09 9:03, David G. Johnston wrote:
> On Tue, Aug 8, 2017 at 4:33 PM, Dean Rasheed wrote:
>> Well perhaps verbosity-reduction isn't sufficient justification but I
>> still think this is correct because logically any values in the bound
>> after MINVALUE/MAXVALUE are irrelevant, so it seems overly restrictive
>> to force all later values to be MINVALUE/MAXVALUE when the code will
>> just ignore those values.
>>
>>
> But semantically using ​unbounded
> is correct - and referencing the principle of "write once, read many"
> people are probably going to care a lot more about the \d display than the
> original SQL - and \d showing some randomly chosen value and mentally doing
> the gymnastics to think "oh, wait, it doesn't actually mater what this
> value is)" compared to it showing "unbounded" and it being obvious that
> "any value" will work, seems like a win.
> 
>> I don't think we should allow values after MINVALUE/MAXVALUE to be
>> omitted altogether because we document multi-column ranges as being
>> most easily understood according to the rules of row-wise comparisons,
>> and they require equal numbers of values in each row.
>>
> 
> I wouldn't change the underlying representation but from a UX perspective
> being able to ?omit the explicit specification and let the system default
> appropriately would be worth considering - though probably not worth the
> effort.
> 
> ​The complaint regarding \d could be solved by figuring out on-the-fly
> whether the particular column is presently bounded or not and diplaying
> "unbounded" for the later ones regardless of what value is stored in the
> catalog.  "Unbounded (0)" would communicate both aspects.​  In the "be
> liberal in what you accept" department that would seem to be a win.

One of the patches posted on the thread where this development occurred
[1] implemented more or less this behavior.  That is, we would let the
users omit inconsequential values in the FROM and TO lists, but internally
store minvalue in the columns for which no value was specified. \d could
show those values as the catalog had it (minvalue).

Consider following example with the current syntax:

create table rp (a int, b int) partition by range (a, b);
create table rp0 partition of rp for values from (minvalue, minvalue) to
(1, minvalue);

\d+ rp0 shows:

Partition of: rp FOR VALUES FROM (MINVALUE, MINVALUE) TO (1, MINVALUE)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a < 1))

With the aforementioned patch, the same partition could be created as:

create table rp0 partition of rp for values from (minvalue) to (1, minvalue);

or:

create table rp0 partition of rp for values from (minvalue) to (1);

Consider one more partition:

create table rp1 partition of rp for values from (1, minvalue) to (1, 1);

\d+ rp1 shows:

Partition of: rp FOR VALUES FROM (1, MINVALUE) TO (1, 1)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 1) AND
(b < 1))

Same could be created with following alternative command:

create table rp1 partition of rp for values from (1) to (1, 1);

Regarding Dean's argument that it's hard to make sense of that syntax when
one considers that row-comparison logic is used which requires equal
number of columns to be present on both sides, since users are always able
to reveal what ROW expression looks like internally by describing a
partition, they can see what values are being used in the row comparisons,
including those of the columns for which they didn't specify any value
when creating the table.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/a6d6b752-3d08-ded8-3c8f-5cc9f090ec20%40lab.ntt.co.jp