From: Jürgen Strobel <juergen+postgresql@strobel.info>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Jürgen Strobel <juergen+postgresql@strobel.info>
Subject: BUG #15212: Default values in partition tables don't work as expected and allow NOT NULL violation
Date: Fri, 9 Nov 2018 17:46:04 +0100
Message-ID: <67125a48-0e0f-4f7e-6d02-e312fe13f7fe@strobel.info> (raw)
In-Reply-To: <20181109155943.xnlbcyzp7crnyjmo@alvherre.pgsql>
References: <20181109155943.xnlbcyzp7crnyjmo@alvherre.pgsql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: juergen+postgresql@strobel.info, alvherre@2ndquadrant.com, Langote_Amit_f8@lab.ntt.co.jp, amitlangote09@gmail.com, ashutosh.bapat@enterprisedb.com, pgsql-hackers@lists.postgresql.org
Subject: Re: BUG #15212: Default values in partition tables don't work as expected and allow NOT NULL violation
In-Reply-To: <67125a48-0e0f-4f7e-6d02-e312fe13f7fe@strobel.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox