Re: UPSERT wiki page, and SQL MERGE syntax

On Wed, Oct 8, 2014 at 6:12 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> 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.

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

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

Wrong.

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

When you make the slightest effort to understand what my approach is,
I might take your remarks seriously.

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

No true Scotsman.

--
Peter Geoghegan