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

On Mon, 2018-11-12 at 11:33 -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > One of the guiding principles that I think we should hold for
> > partitioning is that operating directly into the partition should
> > be
> > seen as only an optimization compared to inserting into the parent
> > table
> > -- thus it should not behave differently. Applying different
> > default
> > values depending on where you're inserting into goes counter to
> > that
> > principle.
>
> I'm not entirely convinced that I buy that argument, especially not
> in a case like this where it introduces logical inconsistencies where
> there otherwise wouldn't be any.
>

Not sure what new logical inconsistencies you have in mind, but the
principle proposed by Alvaro seems sensible to me. I too see insertions
into the partition primarily as an optimization, and would not expect
it to use other defaults than when inserting through the parent.

Particularly not defaults contradicting the partition definition and
forcing rerouting the tuple somewhere else automatically. (IME when
inserting directly into partitions people expect that data to end in
that partition, and if not it's an error - bogus data, ...).

I'm not claiming there are no use cases for defaults on partitions. For
example I can imagine multi-level partitioning, where each layer can
specify defaults for the lower levels. But it seems rather surprising
to only apply the partition defaults for direct insertions.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services