Re: INSERT ... ON CONFLICT UPDATE and RLS

On 6 January 2015 at 20:44, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> Another issue that I see is that there is only one resultRelation
> between the INSERT and UPDATE. That means that without some additional
> special measure, both UPDATE and INSERT "WITH CHECK ( ... ) " options
> are applied regardless of whether the INSERT path was taken, or the
> UPDATE path. Maybe that's actually sensible (note that even this
> doesn't happen right now, since the auxiliary UPDATE is currently
> minimally processed by the rewriter). What do people think about that?
> It seems like it might be less surprising to have that fail earlier
> when there are separate policies for INSERT and UPDATE -- for UPSERT,
> all policies are applied regardless of which path is taken.
>

I think the policies applied should depend on the path taken, so if it
does an INSERT, then only the INSERT CHECK policy should be applied
(after the insert), but if it ends up doing an UPDATE, I would expect
the UPDATE USING policy to be applied (before the update) and the
UPDATE CHECK policy to be applied (after the update). I would not
expect the INSERT CHECK policy to be applied on the UPDATE path.

As to whether the UPDATE USING policy should cause an error to be
thrown if it is not satisfied, my inclination would be to not error,
and use the command tag to report that no rows were updated, since
that is what would happen with a regular UPDATE.

So overall INSERT .. ON CONFLICT UPDATE would be consistent with
either an INSERT or an UPDATE, depending on whether the row existed
beforehand, which is easier to explain than having some special UPSERT
semantics.

Regards,
Dean