Thread: BUG #15212: Default values in partition tables don't work as expectedand allow NOT NULL violation

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. The easiest way to reproduce this is:

jue=> create table ptest (a int, b int) partition by list (a);
CREATE TABLE
jue=> create table ptest1 partition of ptest (b default 7) for values in
(1);
CREATE TABLE
jue=> insert into ptest (a) values (1);
INSERT 0 1
jue=> table ptest;
 a | b 
---+---
 1 |  
(1 row)

jue=> insert into ptest1 (a) values (1);
INSERT 0 1
jue=> table ptest;
 a | b 
---+---
 1 |  
 1 | 7
(2 rows)

Second, this is a way to violate a NOT NULL constraint, presumably because a
default value should be applied later but isn't:

jue=> create table ptest (a int, b int not null) partition by list (a);
CREATE TABLE
jue=> create table ptest1 partition of ptest (b default 7) for values in
(1);
CREATE TABLE
jue=> insert into ptest (a) values (1);
INSERT 0 1
jue=> select * from ptest where b is null;
 a | b 
---+---
 1 |  
(1 row)

The same happens for defaults using nextval(sequence), either if specified
directly or as SERIAL columns with ALTER TABLE ... ATTACH PARTITION. My
current workaround is to use a before-row trigger to apply the default
value.

Hello.

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.

Thank you for reporting this and sorry it took a while to reply here.

> 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. The easiest way to reproduce this is:
> 
> jue=> create table ptest (a int, b int) partition by list (a);
> CREATE TABLE
> jue=> create table ptest1 partition of ptest (b default 7) for values in
> (1);
> CREATE TABLE
> jue=> insert into ptest (a) values (1);
> INSERT 0 1
> jue=> table ptest;
>  a | b 
> ---+---
>  1 |  
> (1 row)
> 
> jue=> insert into ptest1 (a) values (1);
> INSERT 0 1
> jue=> table ptest;
>  a | b 
> ---+---
>  1 |  
>  1 | 7
> (2 rows)
>
> The same happens for defaults using nextval(sequence), either if specified
> directly or as SERIAL columns with ALTER TABLE ... ATTACH PARTITION.

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.

> Second, this is a way to violate a NOT NULL constraint, presumably because a
> default value should be applied later but isn't:
> 
> jue=> create table ptest (a int, b int not null) partition by list (a);
> CREATE TABLE
> jue=> create table ptest1 partition of ptest (b default 7) for values in
> (1);
> CREATE TABLE
> jue=> insert into ptest (a) values (1);
> INSERT 0 1
> jue=> select * from ptest where b is null;
>  a | b 
> ---+---
>  1 |  
> (1 row)

This is clearly a bug of CREATE TABLE .. PARTITION OF.  It seems that the
parent's NOT NULL constraint is not copied to the partition when a clause
to set other column options, such as default 7 above, is used in the
command to create a partition.  It *is* successfully copied when such a
clause is not specified.  For example, same example but without the
default value clause will lead to correct behavior wrt NOT NULL constraint.

create table p (a int, b int not null) partition by list (a);

-- note there is no (b default 7) clause being used here
create table p1 partition of p for values in (1);

-- NOT NULL constraint is correctly enforced
insert into p values (1);
ERROR:  null value in column "b" violates not-null constraint
DETAIL:  Failing row contains (1, null).

Attached patches fix that for PG 10 (patch filename starting with PG10-)
and HEAD branches, respectively.

Thanks,
Amit

> On 6 June 2018 at 10:00, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
> Hello.
>
> 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.
>
> 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.

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

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

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

On Wed, Jun 13, 2018 at 03:39:04PM +0200, Jürgen Strobel wrote:
> 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.

Please note that I have added this thread to the Open item page, under
the older bug queue, and that a CF entry has been added so as we don't
lost track of it:
https://commitfest.postgresql.org/18/1671/
--
Michael

On 2018/06/14 10:57, Michael Paquier wrote:
> On Wed, Jun 13, 2018 at 03:39:04PM +0200, Jürgen Strobel wrote:
>> 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.
> 
> Please note that I have added this thread to the Open item page, under
> the older bug queue, and that a CF entry has been added so as we don't
> lost track of it:
>
> https://commitfest.postgresql.org/18/1671/

Thank you, Michael.

Just to be sure, you meant to add the open item for the NOT NULL violation
bug fix patch mainly or all of what appears to be wrong here?  About
partition-specific defaults being ignored when inserting through the
parent, do you think we should consider it a bug?  I could imagine fixing
the documentation to say that partition-specific defaults are only honored
if directly inserted into and ignored otherwise.

Thanks,
Amit