Re: BEFORE trigger can cause undetected partition constraint violation

On Mon, Jun 5, 2017 at 1:02 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2017/06/03 1:56, Robert Haas wrote:
>> On Fri, Jun 2, 2017 at 12:51 AM, Amit Langote
>> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>> Attached patch makes InitResultRelInfo() *always* initialize the
>>> partition's constraint, that is, regardless of whether insert/copy is
>>> through the parent or directly on the partition. I'm wondering if
>>> ExecInsert() and CopyFrom() should check if it actually needs to execute
>>> the constraint? I mean it's needed if there exists BR insert triggers
>>> which may change the row, but not otherwise. The patch currently does not
>>> implement that check.
>>
>> I think it should. I mean, otherwise we're leaving a
>> probably-material amount of performance on the table.
>
> I agree. Updated the patch to implement the check.

OK, so this isn't quite right. Consider the following test case:

rhaas=# create table x (a int, b int, c int) partition by list (a);
CREATE TABLE
rhaas=# create table y partition of x for values in (1) partition by list (b);
CREATE TABLE
rhaas=# create table z partition of y for values in (1);
CREATE TABLE
rhaas=# insert into y values (2,1,1);
INSERT 0 1

The absence of the before-trigger is treated as evidence that z need
not revalidate the partition constraint, but actually it (or
something) still needs to enforce the part of it that originates from
y's ancestors. In short, this patch would reintroduce the bug that
was fixed in 39162b2030fb0a35a6bb28dc636b5a71b8df8d1c, and would do so
by removing the exact same code that was added (by you!) in that
patch.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company