Re: INSERT ... ON CONFLICT UPDATE and RLS

* Peter Geoghegan (pg(at)heroku(dot)com) wrote:
> On Fri, Jan 9, 2015 at 12:26 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > Where this leaves me, at least, is feeling like we should always apply
> > the INSERT WITH CHECK policy, then if there is a conflict, check the
> > UPDATE USING policy and throw an error if the row isn't visible but
> > otherwise perform the UPDATE and then check the UPDATE WITH CHECK
> > policy. I see your point that this runs counter to the 'mod_count'
> > example use-case and could cause problems for users using RLS with such
> > a strategy. For my part, I expect users of RLS to expect errors in such
> > a case rather than allowing it, but it's certainly a judgement call.
>
> I mostly agree, but I don't know that I fully agree. Specifically, I
> also think we should check the update policy even when we only insert,
> on the theory that if we did go to update, the would-be inserted row
> would be a proxy for what we'd check then (the existing, target
> relation's tuple). What do you think of that?

To flip it around a bit, I don't think we can avoid checking the
*resulting* tuple from the UPDATE against the UPDATE policy. Therefore,
I'm not sure that I see the point in checking the INSERT tuple against
the UPDATE policy. I also don't like the idea that a completely
legitimate command (one which allows the to-be-inserted row via the
INSERT policy AND allows the tuple-post-update via the UPDATE policy) to
throw an error because the to-be-inserted row violated the UPDATE
policy. That doesn't make sense to me.

Thanks!

Stephen