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

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.
>>>
>>> Hmm, so we provide the ability to specify default values per partition,
>>> but it is not applied when inserting through the parent. I'd like to hear
>>> from others on whether we should fix things so that we fill the
>>> partition's default value for a given column if it's null in the input
>>> tuple, after that tuple is routed to that partition. It does seem like a
>>> inconvenience to have to do it through workarounds like a BR trigger.
>>>
>>> Actually, default value substitution happens much earlier in the query
>>> rewrite phase, whereas the partition to actually insert the tuple into
>>> (that is, tuple routing) is determined much later during the execution of
>>> the query. So fixing this will require some work.
>>
>> 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