On Thu, Oct 9, 2014 at 4:25 AM, Peter Geoghegan <p...@heroku.com> wrote: > On Wed, Oct 8, 2014 at 6:12 PM, Marti Raudsepp <ma...@juffo.org> wrote: >> Skipping >> BEFORE UPDATE entirely seems to violate POLA.
> Good thing that the patch doesn't do that, then. I clearly documented > this in a few places, including: > http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/trigger-definition.html I tried to understand the docs and found them somewhat confusing, so I turned to testing the implementation you posted on 2014-10-07. It shows no signs of running UPDATE triggers in the following case. So is this just a bug or a missing feature? create table evt_type (id serial primary key, name text unique, evt_count int, update_count int default 0); prepare upsert(text) as INSERT into evt_type (name, evt_count) values ($1, 1) on conflict within evt_type_name_key UPDATE set evt_count=conflicting(evt_count)+1; create or replace function ins() returns trigger language plpgsql as $$begin raise notice 'trigger % %', TG_WHEN, TG_OP; return new; end$$; create or replace function upd() returns trigger language plpgsql as $$begin raise notice 'trigger % %', TG_WHEN, TG_OP; new.update_count=new.update_count+1; return new; end$$; create trigger ev1 before insert on evt_type execute procedure ins(); create trigger ev2 before update on evt_type execute procedure upd(); create trigger ev3 after insert on evt_type execute procedure ins(); create trigger ev4 after update on evt_type execute procedure upd(); db=# execute upsert('foo'); NOTICE: trigger BEFORE INSERT NOTICE: trigger AFTER INSERT INSERT 0 1 db=# execute upsert('foo'); NOTICE: trigger BEFORE INSERT NOTICE: trigger AFTER INSERT INSERT 0 0 marti=# table evt_type; id | name | evt_count | update_count ----+------+-----------+-------------- 1 | foo | 2 | 0 >> MySQL gets away with lots of things, they have several other caveats > No true Scotsman. Eh? I'm just saying there may be good reasons not to imitate MySQL here. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
