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

On 2018-Nov-09, Amit Langote wrote:

> Or is it a *bug* of tuple-routing that it doesn't substitute default
> values that may be defined for partitions? It kind of looks like one if
> you see an example like this.
>
> create table p (a int, b int) partition by list (a);
> create table p1 partition of p (b not null default 1) for values in (1);
> insert into p1 values (1);
> table p;
> a │ b
> ───┼───
> 1 │ 1
> (1 row)
>
> insert into p values (1);
> ERROR: null value in column "b" violates not-null constraint
> DETAIL: Failing row contains (1, null).

I don't know. I wonder if the bug isn't that we allow the default to be
specified for the partition at all -- why shouldn't we just reject that
with an error?

See this example, where the inserts give values that could be said to be
inconsistent:

create table p (a int , b int default 3) partition by list (a);
create table p1 partition of p (b default 42) for values in (1);
insert into p (a) values (1);
insert into p1 (a) values (1);
select * from p;

a │ b
───┼────
1 │ 3
1 │ 42
(2 filas)

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services