On 14 July 2017 at 06:12, Robert Haas <robertmh...@gmail.com> wrote: > 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
Right. So in general, when using MINVALUE/MAXVALUE for the 2nd column of a 2-column partitioning scheme, the partition constraints simplify as follows: FROM (x, MINVALUE) => col1 >= x FROM (x, MAXVALUE) => col1 > x TO (x, MINVALUE) => col1 < x TO (x, MAXVALUE) => col1 <= x which restores the property that one partition can be made contiguous with another by having the upper bounds of one partition equal to the lower bounds of the other. Note that the choice of MINVALUE or MAXVALUE only affects whether the constraint on the previous column is inclusive or exclusive. That's quite different from what an INCLUSIVE/EXCLUSIVE flag would do. >, 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? The INCLUSIVE/EXCLUSIVE flag would apply to the constraint as a whole: FROM (x, y) INCLUSIVE => (col1, col2) >= (x, y) FROM (x, y) EXCLUSIVE => (col1, col2) > (x, y) TO (x, y) INCLUSIVE => (col1, col2) <= (x, y) TO (x, y) EXCLUSIVE => (col1, col2) < (x, y) which, when expanded out, actually only affects the constraint on the final column, and then only in the case where all the other columns are equal to the partition bound value: FROM (x, y) INCLUSIVE => col1 > x OR (col1 = x AND col2 >= y) FROM (x, y) EXCLUSIVE => col1 > x OR (col1 = x AND col2 > y) TO (x, y) INCLUSIVE => col1 < x OR (col2 = x AND col2 <= y) TO (x, y) EXCLUSIVE => col1 < x OR (col2 = x AND col2 < y) So while MINVALUE/MAXVALUE makes a particular column unbounded below/above, and as a side-effect can influence the inclusivity of the preceding column, INCLUSIVE/EXCLUSIVE affects the inclusivity of the final column (something that MINVALUE/MAXVALUE cannot do). MINVALUE/MAXVALUE takes precedence, in the sense that if the bound on any column is MINVALUE/MAXVALUE, that column and any later columns are unbounded and no longer appear in the partition constraint expression, and so any INCLUSIVE/EXCLUSIVE flag would have no effect. That seems pretty intuitive to me -- "unbounded inclusive" is no different from "unbounded exclusive". Technically, anything that can be done using INCLUSIVE/EXCLUSIVE can also be done using using MINVALUE/MAXVALUE, by artificially adding another partitioning column and making it unbounded above/below, but that would really just be a hack, and it (artificially adding an extra column) would be unnecessary if we added INCLUSIVE/EXCLUSIVE support in a later release. Thus, I think the 2 features would complement each other quite nicely. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
