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

On 2015-05-01 10:39:35 -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 10:24 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > 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.

Well, I think it's pretty unavoidable to fire both. On that part I think
were pretty lengthy discussions a year or so back.

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

I think that'd be much worse.

One question, that I was wondering about earlier, that also might
influence this discussion, is what happens if you change the key we're
using for resolution during either the BEFORE trigger or the ON CONFLICT
... SET. Right now the conflict will be on the version *after* the
BEFORE INSERT, which I think think is the only reasonable option.

And if you're mad enough to change the key in the ON CONFLICT ... SET
... you'll possibly get conflicts. I was, over IM, arguing for that to
be forbidden to protect users against themselves, but Heikki said people
might e.g. want to set a column in a key to NULL in that case.

Greetings,

Andres Freund