Postgresql – insert is not working as expected while doing partitioning

partitioningpostgresqltrigger

Created a table with one column and sample values

create table t1 ();
alter table t1 add column id_ numeric;
insert into t1 values (generate_series(1,10));

And, done partitioning odd numbers in one table and even numbers in one table.

create table t1_odd (check (id_%2!=0)) inherits (t1);
create table t1_even (check (id_%2=0)) inherits (t1);

create table t1_r15 (check (id_<6)) inherits (t1);
create table t1_r510 (check (id_>4)) inherits (t1);

Call this function while insert or update anything in table t1

create or replace function t1_func() returns trigger as $$
begin
    if NEW.id_ % 2 != 0 then
       insert into t1_odd values (NEW.*);
    else
       insert into t1_even values (NEW.*);
    end if;
    return null;
end;
$$
language plpgsql;

create trigger t1_trig before insert or update on t1 for each row execute procedure t1_func();

Call this function while delete anything from table t1

create or replace function t1_rfunc() returns trigger as $$
begin
    if NEW.id_ < 5 then
        insert into t1_r15 values (NEW.*);
    else
        insert into t1_r510 values (NEW.*);
    end if;
    return null;
end;
$$
language plpgsql;

create trigger t1_rtrig before delete on t1 for each row execute procedure t1_rfunc();

It works fine for insert, and If i tried to update, getting some error

update t1 set id_ = id_ + 1;

gives

ERROR: new row for relation "t1_odd" violates check constraint "t1_odd_id__check"
SQL state: 23514
Detail: Failing row contains (2).

and while doing delete nothing gets inserted into t1_r15 or t1_r510 (data got removed from t1).

Am I missing anything?

(Using Postgresql 9.5)

Best Answer

Well, with the checks you use for your child tables, it is obvious why such an UPDATE runs into problems. Adding one to id_ changes it from even to odd or back - the new row does not match the check anymore.

The first trigger is not enough to circumvent this as it does not remove the old row from the child table. You need a mechanism that does exactly this - delete the old row version and readd the new to either t1 or the matching child. This trigger has to be defined on t1_odd and t2_even.