Re: UPSERT wiki page, and SQL MERGE syntax

On Thu, Oct 9, 2014 at 3:47 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Wed, Oct 8, 2014 at 5:37 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>> Only in case the trigger changes *key* columns necessary for atomicity
>> (i.e. from the WITHIN index). Other columns are fair game. The
>> restriction seems justifiable to me: it's unreasonable to be atomic
>> with respect to values that change mid-way.

Oh, one more consideration: I believe you will run into the same issue
if you want to implement BEFORE UPDATE triggers in any form. Skipping
BEFORE UPDATE entirely seems to violate POLA.

It's common for applications to e.g. use triggers to keep track of
latest modified time for a row. With your proposal, every query needs
to include logic for that to work.

>> If you don't see any reasons why it can't be done, these benefits seem
>> clear to me. I think the tradeoffs at least warrant wider discussion.
>
> I don't. That's very surprising. One day, it will fail unexpectedly.
> As proposed, the way BEFORE INSERT triggers fire almost forces users
> to consider the issues up-front.

Not necessarily "up-front", as proposed it causes existing triggers to
change behavior when users adopt the upsert feature. And that adoption
may even be transparent to the user due to ORM magic.

There are potential surprises with both approaches. Personally I
prefer a hard error instead of silently skipping logic that previously
worked. And that seems to match general PostgreSQL philosophy too.

> Note that the CONFLICTING() behavior with respect to BEFORE INSERT
> triggers work's the same as MySQL's "INSERT ON DUPLICATE KEY UPDATE
> foo = VALUES(foo)" thing. There was agreement that that was the right
> behavior, it seemed.

MySQL gets away with lots of things, they have several other caveats
with triggers. I don't think it's a good example to follow wrt trigger
behavior.

Regards,
Marti