Re: Declarative partitioning - another take

On Thu, Nov 24, 2016 at 6:13 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2016/11/23 4:50, Robert Haas wrote:
>> On Tue, Nov 22, 2016 at 4:15 AM, Amit Langote
>> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>>> The easiest thing to do might be to just enforce that all of the
>>>> partition key columns have to be not-null when the range-partitioned
>>>> table is defined, and reject any attempt to DROP NOT NULL on them
>>>> later.  That's probably better that shoehorning it into the table
>>>> constraint.
>>>
>>> Agreed that forcing range partitioning columns to be NOT NULL during table
>>> creation would be a better approach.  But then we would have to reject
>>> using expressions in the range partition key, right?
>>
>> Why?
>
> I was thinking of it like how primary key columns cannot contain
> expressions; the reason for which, I assume, is because while we can
> ensure that a column contains only non-null values by defining a
> constraint on the column, there is no way to force expressions to be non-null.
>
> In any case, I have implemented in the latest patch that when creating a
> range partitioned table, its partition key columns are automatically set
> to be NOT NULL.  Although, it leaves out the columns that are referenced
> in expressions.  So even after doing so, we need to check after computing
> the range partition key of a input row, that none of the partitions keys
> is null, because expressions can still return null.

Right.  And ensuring that those columns were NOT NULL would be wrong,
as it wouldn't guarantee a non-null result anyway.

> 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.

> 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.

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