Re: UPSERT wiki page, and SQL MERGE syntax

On Thu, Oct 9, 2014 at 4:25 AM, Peter Geoghegan <pg@heroku.com> wrote:
> On Wed, Oct 8, 2014 at 6:12 PM, Marti Raudsepp <marti@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