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
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 |