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 toid_
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 ont1_odd
andt2_even
.