Re: UPSERT wiki page, and SQL MERGE syntax

Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Fri, Oct 10, 2014 at 11:30 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

>> That seems a lot cleaner than the proposal on the Wiki page. If we
>> go that route, it makes sense to fire the BEFORE INSERT triggers
>> before attempting the insert and then fire BEFORE UPDATE triggers
>> before attempting the UPDATE. If either succeeds, I think we
>> should fire the corresponding AFTER triggers. We already allow a
>> BEFORE triggers to run and then omit the triggering operation
>> without an error, so I don't see that as a problem. This makes a
>> lot more sense to me than attempting to add a new UPSERT trigger
>> type.
>
> You realize that that's exactly what my patch does, right?

I haven't read the patch, but the discussion has made that clear,
yes. Try to take agreement on a point gracefully, will ya? ;-)

> AFAICT the only confusion that my patch has is with statement-level
> triggers, as discussed on the Wiki page. I think that the row-level
> trigger behavior is fine; a lot more thought has gone into it.

IMV it is clear that since the standard says that update or insert
operations which affect zero rows fire the corresponding trigger,
the statement level triggers for both should fire for an UPSERT,
even if the set of affected rows for one or the other (or both!) is
zero. If we ever get transition relations, it will be easy to
check the count of affected rows or otherwise behave appropriately
based on what was done.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company