Re: INSERT ... ON CONFLICT UPDATE and RLS

On Wed, Jan 7, 2015 at 4:04 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> 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.

I agree.

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

My inclination would be to error, but I'd be OK with your proposal.

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

Yeah. We won't escape the question so easily where triggers are
concerned, but at least for RLS it seems like it should be possible to
avoid confusing, one-off semantics.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company