hi there!
for me case listed below looks like something goes wrong (at least very strange)
pg 9.2.10
* schema:
base_table ==> inheritance_with_before_trigger ==> child_table ==> audit_after_trigger ==> child_table_log
* init sql:
create schema tmp;
--create extension hstore;
-- inheritance part
drop table if exists tmp.test_trg; -- child_table
drop table if exists tmp.test_trg_0; -- base_table
create table tmp.test_trg_0( id serial primary key, tag text );
create table tmp.test_trg( like tmp.test_trg_0 including all ) inherits ( tmp.test_trg_0 );
create or replace function tmp.test_trg_inh_func() returns trigger language 'plpgsql' as
$$
begin
insert into tmp.test_trg select NEW.*;
--raise notice 'inh % %', TG_TABLE_NAME, NEW.id;
return null;
end;
$$;
create trigger test_trg_inh before insert on tmp.test_trg_0 for each row execute procedure tmp.test_trg_inh_func();
-- audit part
drop table if exists tmp.test_trg_log;
create table tmp.test_trg_log( ev_id serial primary key, txid bigint default txid_current(), xdata text );
create or replace function tmp.test_trg_func() returns trigger language 'plpgsql' as
$$
begin
insert into tmp.test_trg_log ( xdata ) select TG_OP || ' ' || hstore( case when TG_OP = 'DELETE' then OLD else NEW end );
--raise notice 'log % %', TG_TABLE_NAME, TG_OP;
return null;
end;
$$
;
create trigger test_trg after insert or update or delete on tmp.test_trg for row execute procedure tmp.test_trg_func(); -- log on child
-- populate
--insert into base_table
insert into tmp.test_trg_0 ( tag ) select oid::text from pg_class;
* action sql:
-- delete then insert from/into base_table
with
del as ( delete from tmp.test_trg_0 t where t.id between 15 and 16 returning t.* )
insert into tmp.test_trg_0
select * from del
;
* and data from audit table
select * from ( select * from tmp.test_trg_log order by ev_id desc limit 10 )_ order by ev_id
see on order of events -- INSERTs come first before DELETEs!
is it expectable performance? any explanation?
it is also could be a important issue for trigger-based replication systems.
--misha