writable cte triggers reverse order

From: Миша Тюрин <tmihail(at)bk(dot)ru>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Cc: Sergey Burladyan <eshkinkot(at)gmail(dot)com>, Marko Kreen <markokr(at)gmail(dot)com>
Subject: writable cte triggers reverse order
Date: 2015-06-17 21:14:20
Message-ID: 1434575660.154745800@f403.i.mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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


Responses

Browse pgsql-general by date

  From Date Subject
Next Message Noah Misch 2015-06-18 00:47:56 Re: [GENERAL] psql weird behaviour with charset encodings
Previous Message William Dunn 2015-06-17 20:52:37 Re: pg_class.reltuples VS pg_stat_all_tables.n_live_tup for estimation of table