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

On 2018-11-09 16:59, Alvaro Herrera wrote:
> 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)

I found this problem while attempting to create sub-ids with partition
defaults using distinct per-partition sequences.

I can think of other useful scenarios too, but if you don't want to
support it because of unexpected complexities all my cases can still be
implemented using custom triggers albeit slower and more inconvenient.

Regarding your example, what I expected is that *both* inserts would
consistently result in a tuple of (1, 42) since p should route the
insert to p1 and use p1's defaults. The current inconsistent behavior is
the heart of the bug.

Best regards,
Jürgen