On 2018/11/10 7:33, Tom Lane wrote: > Alvaro Herrera <alvhe...@2ndquadrant.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.
Considering multi-level partitioning, that'd mean the tuple being routed would need to be filled with the defaults of every table on the way to a leaf partition, including that of the leaf partition where the tuple finally ends up. If we re-route upon the final leaf partition's defaults leading to partition constraint violation, it's possible that someone might end up setting up an infinite re-routing loop with that behavior. create table p (a int) partition by list (a); create table p1 partition of p (a default 2) for values in (1); create table p2 partition of p (a default 1) for values in (2); It won't be fun for the server to try to prevent that kind of thing. IOW, it might be a good idea to call the ability to set partition-level defaults a deprecated feature? Thanks, Amit
