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