Re: BEFORE trigger can cause undetected partition constraint violation

I tried to debug this, and I see that while the target partition index is
correctly
found in ExecInsert(), somehow the resultRelInfo->ri_PartitionCheck is NIL,
this
is extracted from array mstate->mt_partitions.

This prevents execution of constraints in following code snippet in
ExecInsert():

/*
* Check the constraints of the tuple
*/
if (resultRelationDesc->rd_att->constr || resultRelInfo->ri_PartitionCheck)
ExecConstraints(resultRelInfo, slot, estate);

I couldn't debug it further today.

Regards,
Jeevan Ladhe

On Fri, Jun 2, 2017 at 1:21 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I just discovered that a BEFORE trigger can allow data into a
> partition that violates the relevant partition constraint. This is
> bad.
>
> Here is an example:
>
> rhaas=# create or replace function t() returns trigger as $$begin
> new.a := 2; return new; end$$ language plpgsql;
> CREATE FUNCTION
> rhaas=# create table foo (a int, b text) partition by list (a);
> CREATE TABLE
> rhaas=# create table foo1 partition of foo for values in (1);
> CREATE TABLE
> rhaas=# create trigger x before insert on foo1 for each row execute
> procedure t();
> CREATE TRIGGER
> rhaas=# insert into foo values (1, 'hi there');
> INSERT 0 1
> rhaas=# select tableoid::regclass, * from foo;
> tableoid | a | b
> ----------+---+----------
> foo1 | 2 | hi there
> (1 row)
>
> That row violates the partition constraint, which requires that a = 1.
> You can see that by trying to insert the same row into the partition
> directly:
>
> rhaas=# insert into foo1 values (2, 'hi there');
> ERROR: new row for relation "foo1" violates partition constraint
> DETAIL: Failing row contains (2, hi there).
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>