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

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> 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.

I agree that having the before-insert and before-update triggers both
fire is a bit odd, but I also think it's the right thing to do. If the
statements were independent instead of an INSERT .. ON CONFLICT, then
both sets of before triggers would very clearly fire.

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

Another option would be to have an independent "INSERT-ON-CONFLICT"
before trigger which fires.. but, for my 2c, I'm happy to just fire
both.

I definitely feel that the EXCLUDED tuple should refer to the post
before-insert trigger; having it refer to a tuple that may not have
actually conflicted doesn't seem correct to me.

Thanks!

Stephen