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

From: Jürgen Strobel <juergen+postgresql(at)strobel(dot)info>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Amit Langote <amitlangote09(at)gmail(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #15212: Default values in partition tables don't work as expected and allow NOT NULL violation
Date: 2018-11-12 13:52:11
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs pgsql-hackers

On 2018-11-09 23:33, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>> On 2018-Nov-09, Jürgen Strobel wrote:
>>> 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.
>> I think that would be sensible behavior, as long as the partition
>> doesn't override values for the partitioning column -- i.e. if the
>> default values don't re-route the tuple to another partition, I think we
>> should use the partition's default rather than the parent. This says we
>> should expand defaults after routing.
> I'd argue not, actually. I think there is plausible precedent in
> updatable views, where what we use is the defaults associated with the
> view, not the underlying table. Correspondingly, what ought to govern
> in a partitioned insert is the defaults associated with the table actually
> named in the insert command, never mind what its partitions might say.
> That is useful for many situations, and it avoids all the logical
> inconsistencies you get into if you find that the defaults associated
> with some partition would force re-routing elsewhere.
> In any case, you can't make this happen without basically blowing up
> default processing altogether. Defaults are currently expanded by the
> planner, and pretty early too. To make it work like the OP wishes,
> we'd have to insert them sometime late in the executor.
>> In any case it seems really hard to see this is as a bug that we would
>> fix in back-branches.
> Backwards compatibility considerations would prevent that in any case.
I don't really think the view comparison holds water. Views are layered
above tables without affecting them by design, but partitions are
intrinsic parts of sharded tables and only make sense as a whole.

With all due respect I suspect your view of implementation issues biases
your judgement here. For me the partition's default handling was
completely unexpected, especially with the documentation silent about
default-applying behavior, and inconsistent with how check constraints
work with partitions.

Maybe there's another way to implement this. Like mark early and apply
defaults later, and handle meta data like constraints. Sorry if this
sounds stupid.

Btw I fully agree about not back-patching changes to this, but a
documentation update would be nice.

Best regards,

In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2018-11-12 14:00:51 Re: BUG #15212: Default values in partition tables don't work as expected and allow NOT NULL violation
Previous Message Etsuro Fujita 2018-11-12 11:41:40 Re: BUG #15449: file_fdw using program cause exit code error when using LIMIT

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2018-11-12 13:55:41 Re: move PartitionBoundInfo creation code
Previous Message Peter Eisentraut 2018-11-12 13:46:07 Re: Small run-time pruning doc fix