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

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