Question :
I have created a partitioned table (base
), and an audit table for it(base_audit
) and then created child tables (c1,c2,c3,..cn
). To populate entries in base_audit
, I have created a function and a row level trigger as below.
--Initial tables and partitions
create table base(id bigint, val text) partition by range (id);
create table base_audit(id bigint, val text, act char);
create table c1 partition of base for values from (1) to (3);
create table c2 partition of base for values from (4) to (6);
create table c3 partition of base for values from (7) to (10);
--Trigger function
CREATE OR REPLACE FUNCTION base_function() RETURNS trigger AS $$
BEGIN
if (tg_op = 'UPDATE') then
if (old.id = new.id) then
insert into base_audit values (old.id, oid.val, 'u');
return new;
else
insert into base_audit values (old.id, oid.val, 'i');
insert into base_audit values (new.id, new.val, 'a');
return new;
end if;
elseif (tg_op = 'DELETE') then
insert into base_audit values (old.id, oid.val, 'd');
return old;
end if;
return new;
END; $$ LANGUAGE plpgsql;
--Trigger
CREATE TRIGGER base_trigger BEFORE INSERT OR DELETE OR UPDATE ON base FOR EACH row EXECUTE PROCEDURE base_function();
When I try to run the above create trigger sql, I am getting the below exception
ERROR: "base" is a partitioned table
DETAIL: Partitioned tables cannot have ROW triggers.
SQL state: 42809
I couldn’t get what I am missing here. Is it something to do with Aurora Postgresql?
(Using Aurora Postgresql 10.7)
Answer :
I don’t know about Aurora, but on recent PostgreSQL your trigger will work just fine if you define it to fire AFTER
rather than BEFORE
the DML operation. That should work just as well.
Triggers on partitioned tables were introduced in v11 – is upgrading an option?