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

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(at)strobel(dot)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