Re: [HACKERS] BEFORE trigger can cause undetected partitionconstraint violation - Mailing list pgsql-hackers

From Jeevan Ladhe
Subject Re: [HACKERS] BEFORE trigger can cause undetected partitionconstraint violation
Date
Msg-id CAOgcT0POap4MPR_dV=w_pAkaGfJWXZ+nCBJ87B=Q5d5aiFNLTQ@mail.gmail.com
Whole thread Raw
In response to [HACKERS] BEFORE trigger can cause undetected partition constraint violation  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
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@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: [HACKERS] COPY (query) TO ... doesn't allow parallelism
Next
From: Andres Freund
Date:
Subject: Re: [HACKERS] [BUGS] Concurrent ALTER SEQUENCE RESTART Regression