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

On Fri, May 1, 2015 at 7:49 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> 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.

A question has come up about RTEs, column-level privileges and BEFORE
triggers. This commit message gives a summary:

https://github.com/petergeoghegan/postgres/commit/87b9f27055e81d1396db3d10a5e9d01c52603783

I'm pretty sure that I'll have to require SELECT privileges of the
EXCLUDED.* RTE too (i.e. revert that commit, and probably document the
new behavior of requiring that). I think it's worth getting feedback
on this point, though, since it is a somewhat subtle issue.

--
Peter Geoghegan