Re: "cannot specify finite value after UNBOUNDED" ... uh, why?

On Tue, May 30, 2017 at 11:03 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, May 29, 2017 at 3:04 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Would someone please defend the restrictions imposed by the
>>> "seen_unbounded" checks in transformPartitionBound
>>> (parse_utilcmd.c:3365..3396 in current HEAD)?
>
>> Because this is supposed to work more or less like row-comparison --
>> the earlier columns are strictly more significant than the later ones.
>> That is, allowing (1, 2) through (3, 4) allows (2, whatever) but (1,
>> y) only if y >= 2 and (3, y) only if y < 4.
>
> I see. That makes the logic awfully complex though. I was looking
> at get_qual_for_range() yesterday --- it's mind-bendingly complicated
> and I have next to no faith that it's 100% right.

It might be useful if somebody would be willing to put together a
fuzz-tester for it. Like, randomly generate partition bounds over
various different data types, and then verify that each tuple is
accepted by the partition constraint of only one partition and that
it's the same partition into which tuple routing tries to put it.

>> In case you're wondering, this is also how a certain large commercial
>> database system interprets composite bounds. You could imagine in
>> theory a system where a bound from (1, 2) to (3, 4) allows only those
>> (x, y) where 1<=x<3 and 2<=y<4 but I know of no existing system that
>> does anything like that. If you want that sort of thing, you can get
>> it anyway using two levels of partitioning, one on each column.
>
> Well, if we just treated each column independently, you could get
> the row-comparison behavior by partitioning on a ROW() expression.
> So that argument doesn't impress me. I suppose compatibility with
> other partitioning implementations is worth something, but I'm not
> sure it's worth this much complication and risk of bugs.

I guess you won't be terribly surprised to hear that I think
compatibility with other implementations is quite desirable. I also
think that Amit and others who have been involved in the discussions
have the same opinion, although of course they can speak for
themselves. In terms of partitioning on a ROW() expression, I believe
that you would lose the ability to control which opclass and collation
is used for each column, which I think is something that people care
about. Also, I bet it would be slower. Also, I bet the syntax would
be less intuitive.

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