Re: UPSERT wiki page, and SQL MERGE syntax

On Thu, Oct 9, 2014 at 2:46 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>> Indeed, the current behavior breaks even the canonical "keep track of
>> how many posts are in a thread" trigger example
> use an AFTER trigger for this kind of thing, and all of these
> issues go away.

In the latest patches from CommitFest, the UPDATE case invokes
BEFORE&AFTER INSERT triggers, not AFTER UPDATE. Is that going to be
changed? If so, I concede that.

>> I proposed an alternative that avoids this surprise and might allow
>> some other benefits. Can you please look into that?
>
> I looked at that.

Thanks!

> You're talking about making the case where before
> row triggers clearly are appropriate (when you just want to change the
> tuple being inserted) fail

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.

* It can distinguish between BEFORE INSERT and BEFORE UPDATE triggers,
which your approach fundamentally cannot.
* It can probably avoid evaluating column defaults in the UPDATE case,
like nextval() for unrelated serial columns => reduced overhead
* The semantics match better with MERGE-like syntax that seems to come
up again and again.
* And it appears to solve (part?) of the problem you had with naming
key *colums* in the WITHIN clause, rather than using index name.

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.

Regards,
Marti