Обсуждение: writable cte triggers reverse order

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

=?UTF-8?B?0JzQuNGI0LAg0KLRjtGA0LjQvQ==?= <tmihail@bk.ru> writes:
> for me case listed below looks like something goes wrong (at least very strange)
> ...
> see on order of events -- INSERTs come first before DELETEs!

The reason for this is that the AFTER INSERT trigger events are queued
within the INSERT statements executed by the test_trg_inh_func function,
and then executed at the completions of those INSERT statements.  On
the other hand, the AFTER DELETE trigger events are queued by the outer
WITH ... DELETE ... INSERT statement, so they fire at the completion
of that statement.

You could probably get the behavior you want if you make all the triggers
DEFERRED so that they all fire at end-of-transaction, rather than at end
of the statement that queued them.

            regards, tom lane