Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

On Fri, May 1, 2015 at 10:24 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> > Well, it's a BEFORE INSERT trigger, not a BEFORE UPDATE, that's why I'm
>> > not so sure that argument applies.
>>
>> Would the BEFORE UPDATE trigger even fire in this case?
>
> BEFORE UPDATE triggers fire for INSERT ... ON CONFLICT UPDATE iff
> there's a conflict, yes.
>
>> The thing is, suppose somebody puts a BEFORE INSERT trigger and a
>> BEFORE UPDATE trigger on the table, and each of those triggers does
>> this:
>>
>> NEW.last_updated_time = clock_timestamp();
>> return NEW;
>>
>> That should work, and should cover all cases, even if you're using UPSERT.
>
> The BEFORE UPDATE would catch things in this case.

OK. In that case, I'm a lot less sure what the right decision is. It
seems weird for both the BEFORE INSERT and BEFORE UPDATE triggers to
get a crack at the same tuple, so your way might be better after all.
But on the other hand, the BEFORE INSERT trigger might have had side
effects, so we can't just pretend it didn't happen.

One idea is to decide that an INSERT with an ON CONFLICT UPDATE
handler is still an INSERT. Period. So the INSERT triggers run, the
UPDATE triggers don't, and that's it.

Not sure.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company