Re: BUG #15212: Default values in partition tables don't work as expected 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(at)gmail(dot)com> wrote:
>>>> On 6 June 2018 at 10:00, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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(at)strobel(dot)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