Re: NOT NULL constraints on range partition key columns

On Mon, May 15, 2017 at 11:46 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> Starting a new thread to discuss the proposal I put forward in [1] to stop
> creating explicit NOT NULL constraint on range partition keys that are
> simple columns. I said the following:
>
> On 2017/05/12 11:20, Robert Haas wrote:
>> On Thu, May 11, 2017 at 10:15 PM, Amit Langote
>> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>> On 2017/05/12 10:42, Robert Haas wrote:
>>>> Actually, I think that not supporting nulls for range partitioning may
>>>> have been a fairly bad decision.
>>>
>>> I think the relevant discussion concluded [1] that way, because we
>>> couldn't decide which interface to provide for specifying where NULLs are
>>> placed or because we decided to think about it later.
>>
>> Yeah, but I have a feeling that marking the columns NOT NULL is going
>> to make it really hard to support that in the future when we get the
>> syntax hammered out. If it had only affected the partition
>> constraints that'd be different.
>
> So, adding keycol IS NOT NULL (like we currently do for expressions) in
> the implicit partition constraint would be more future-proof than
> generating an actual catalogued NOT NULL constraint on the keycol? I now
> tend to think it would be better. Directly inserting into a range
> partition with a NULL value for a column currently generates a "null value
> in column \"%s\" violates not-null constraint" instead of perhaps more
> relevant "new row for relation \"%s\" violates partition constraint".
> That said, we *do* document the fact that a NOT NULL constraint is added
> on range key columns, but we might as well document instead that we don't
> currently support routing tuples with NULL values in the partition key
> through a range-partitioned table and so NULL values cause error.
>

Can't we allow NULL to get inserted into the partition (leaf
partition) if the user uses the partition name in Insert statement?
For root partitions, I think for now giving an error is okay, but once
we have default partitions (Rahila's patch), we can route NULLS to
default partition.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com