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
