Re: BUG #15212: Default values in partition tables don't work asexpected and allow NOT NULL violation

On 06/13/2018 11:42 AM, Amit Langote wrote:
> Hi.
>
> On 2018/06/07 23:08, Dmitry Dolgov wrote:
>>> On 7 June 2018 at 15:53, Dmitry Dolgov <9erthalion6@gmail.com> wrote:
>>>> On 6 June 2018 at 10:00, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>>> On 2018/05/28 9:30, PG Bug reporting form wrote:
>>>>> The following bug has been logged on the website:
>>>>>
>>>>> Bug reference:      15212
>>>>> Logged by:          Jürgen Strobel
>>>>> Email address:      juergen+postgresql@strobel.info
>>>>> PostgreSQL version: 10.4
>>>>> Operating system:   Debian
>>>>> Description:
>>>>>
>>>>> I found unexpected behavior when playing around with declarative
>>>>> partitioning.
>>>>> First, any way to define defaults on (child) partition tables is silently
>>>>> ignored when inserting into the master table, but not when inserting into
>>>>> the child table.
...
>>>
>>> Well, since documentation says that partitioning build on top of inheritance,
>>> and for inheritance:
>>>
>>>     If the new table explicitly specifies a default value for the column, this
>>>     default overrides any defaults from inherited declarations of the column.
>>>
>>> So one may think it should be the same for partitioning as well.
>>
>> "The same for partitioning" - I mean the same approach when in all the
>> situations (whether it's an insert into a parent table or a partition) a
>> partition default value will take precedence.
>
> I think you have a point.  Before partitioning, one would either insert
> directly into the child table or use a trigger to redirect an insert on
> parent into one of the child tables.  In both cases, child table's default
> value would be used, because the insert query would mention the child
> table name.
>
> With partitioning, inserts into parent are internally handled in a way
> that bypasses the processing which would otherwise fill a partition's own
> default values for columns whose value is missing in the input row.
>
> That said, I'd like to make sure before writing a patch if the feature of
> being able to set defaults on partition level is something that users will
> want in the long run.
>
> Thanks,
> Amit
>

I agree, and I imagine especially being able to use per-partition
sequences would be a common use case. That was my motivation when I
discovered it, and it was very counter intuitive to me.

Thanks for the NULL violation patch btw.

Best regards,
Jürgen