On 2017/05/29 16:04, Tom Lane wrote:
> Would someone please defend the restrictions imposed by the
> "seen_unbounded" checks in transformPartitionBound
> (parse_utilcmd.c:3365..3396 in current HEAD)? They sure look to me like
> nothing but sloppy thinking, and/or protection of downstream sloppy
> thinking. Why should the boundedness of one partitioning column's
> range matter to any other partitioning column's range?
If an earlier column's value is unbounded per the bound specification of a
range partition, later columns of an input row would never be compared
against their respective partition bounds, because the row's partition key
would trivially have been determined to be greater or less than the
partition's lower or upper bound, respectively, based on the comparison
against aforementioned unbounded column's infinite value. Note that it's
the row comparison logic at work here.
Having said that, having finite values after an unbounded value doesn't
really affect tuple-routing code per se (which is what the above paragraph
is meant to describe the actions of), but it does affect the code that
creates constraint expression from the relpartbound value of a range
partition, at least because of the way get_qual_for_range() implements it.
If we had allowed them, an incorrect constraint expression would result as
illustrated below; consider a range partition:
create table foo (a int, b int) partition by range (a, b);
create table foo1 partition of foo for values in (unbounded, -1) to
(unbounded, 1);
foo1, as defined above, basically accepts any row with non-null a, if
inserted through foo via tuple-routing, because any non-null value of a is
trivially >= -infinity and < +infinity.
But, get_qual_for_range() would end up returning an expression that looks
like:
(b >= -1) and (b < 1)
which means that (2, 1) would fail to be inserted directly into foo1 due
to the above constraint, even if inserting it through foo would work,
because column b would not be considered in the latter.
Maybe there is a way to rewrite the code that generates the constraint
expression to somehow not emit the above expression, but any new code to
accomplish that might be more complicated than it is now.
See the following message:
https://www.postgresql.org/message-id/CA%2BTgmoYWnV2GMnYLG-Czsix-E1WGAbo4D%2B0tx7t9NdfYBDMFsA%40mail.gmail.com
I tend to agree with what Robert said there in the first part; there is no
point in allowing finite values to be specified after the last UNBOUNDED
in FROM and TO lists, which might mislead the user to believe that they
are actually significant.
Thanks,
Amit
