Re: Declarative partitioning - another take

On 2016/11/25 11:44, Robert Haas wrote:
> On Thu, Nov 24, 2016 at 6:13 AM, Amit Langote wrote:
>> Also, it does nothing to help the undesirable situation that one can
>> insert a row with a null partition key (expression) into any of the range
>> partitions if targeted directly, because of how ExecQual() handles
>> nullable constraint expressions (treats null value as satisfying the
>> partition constraint).
>
> That's going to have to be fixed somehow. How bad would it be if we
> passed ExecQual's third argument as false for partition constraints?
> Or else you could generate the actual constraint as expr IS NOT NULL
> AND expr >= lb AND expr < ub.

About the former, I think that might work. If a column is NULL, it would
be caught in ExecConstraints() even before ExecQual() is called, because
of the NOT NULL constraint. If an expression is NULL, or for some reason,
the partitioning operator (=, >=, or <) returned NULL even for a non-NULL
column or expression, then ExecQual() would fail if we passed false for
resultForNull. Not sure if that would be violating the SQL specification
though.

The latter would work too. But I guess we would only emit expr IS NOT
NULL, not column IS NOT NULL, because columns are covered by NOT NULL
constraints.

>> An alternative possibly worth considering might be to somehow handle the
>> null range partition keys within the logic to compare against range bound
>> datums. It looks like other databases will map the rows containing nulls
>> to the unbounded partition. One database allows specifying NULLS
>> FIRST/LAST and maps a row containing null key to the partition with
>> -infinity as the lower bound or +infinity as the upper bound, respectively
>> with NULLS LAST the default behavior.
>
> It seems more future-proof not to allow NULLs at all for now, and
> figure out what if anything we want to do about that later. I mean,
> with the syntax we've got here, anything else is basically deciding
> whether NULL is the lowest value or the highest value. It would be
> convenient for my employer if we made the same decision that Oracle
> did, here, but it doesn't really seem like the PostgreSQL way - or to
> put that another way, it's really ugly and unprincipled. So I
> recommend we decide for now that a partitioning column can't be null
> and a partitioning expression can't evaluate to NULL. If it does,
> ERROR.

OK, we can decide later if we want to handle NULLs somehow.

Thanks,
Amit