From: =?UTF-8?Q?J=c3=bcrgen_Strobel?= <juergen+postgresql@strobel.info>
Subject: BUG #15212: Default values in partition tables don't work as expected
 and allow NOT NULL violation
To: Alvaro Herrera <alvherre@2ndquadrant.com>,
 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Amit Langote <amitlangote09@gmail.com>,
 Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 =?UTF-8?Q?J=c3=bcrgen_Strobel?= <juergen+postgresql@strobel.info>
References: <20181109155943.xnlbcyzp7crnyjmo@alvherre.pgsql>
Message-ID: <67125a48-0e0f-4f7e-6d02-e312fe13f7fe@strobel.info>
Date: Fri, 9 Nov 2018 17:46:04 +0100
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <20181109155943.xnlbcyzp7crnyjmo@alvherre.pgsql>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

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