Subject: Re: BUG #15212: Default values in partition tables don't work as
 expected and allow NOT NULL violation
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <20181112140051.yv5m77esq5jz6rkg@alvherre.pgsql>
 <5662.1542040384@sss.pgh.pa.us>
 <ebcf4a32-cfee-8a8f-525d-6ceaa72c75d8@strobel.info>
 <10299.1542067039@sss.pgh.pa.us>
From: =?UTF-8?Q?J=c3=bcrgen_Strobel?= <juergen+postgresql@strobel.info>
Message-ID: <dc3d80bb-87ab-496b-b6ce-396bba0eccda@strobel.info>
Date: Tue, 13 Nov 2018 13:44:03 +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: <10299.1542067039@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 2018-11-13 00:57, Tom Lane wrote:
> =?UTF-8?Q?J=c3=bcrgen_Strobel?= <juergen+postgresql@strobel.info> writes:
>> On 2018-11-12 17:33, Tom Lane wrote:
>>> 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.
> 
>> I think I missed something, what are the *introduced* logical problems?
> 
> What to do if a partition introduces a default value that would force
> re-routing to some other partition.

I would claim this is a problem which already exists with the current
behavior:

a) If a tuple is inserted through the parent it's ignored and useless.
b) If a tuple is inserted through the partition it can lead to
inconsistency and a constraint violation (possibly rectifiable by a
re-route which I think we all agree is out of the question).

>> Apart from implementation issues the only logical problems I see are if
>> you allow to change defaults of the partition key columns, and these are
>> problematic (nonsensical really) in either case.
> 
> Just claiming that it's nonsensical doesn't fix the problem.  Also, it
> is neither problematic nor nonsensical for the root to provide defaults
> for partition key columns.  So if we go this route, we are giving up
> useful behavior (key-column defaults on the root) for useless behavior
> (key-column defaults on the partitions).

Yes, I strongly believe defaults on the key columns in partitions should
be disallowed, that's why I called them nonsensical in both cases, see
above a) and b) for why this applies even to current behavior.

Of course disallowing all DEFAULT clauses on partitions is the easiest
way out, and I'd rather see this than inserts through partitions being
more than a pure optimization.

Best regards,
Jürgen