Re: INSERT ... ON CONFLICT UPDATE and RLS

On Fri, Jan 9, 2015 at 12:19 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> I was trying to think up an example where you might actually have
> different INSERT and UPDATE policies, and the best I can think of is
> some sort of mod_count column where you have an INSERT CHECK
> (mod_count = 0) and an UPDATE CHECK (mod_count > 0). In that case,
> checking both policies would make an UPSERT impossible, whereas if you
> think of it as doing either an INSERT or an UPDATE, as the syntax
> suggests, it becomes possible.

Why does this user want to do this upsert? If they're upserting, then
the inserted row could only reasonably have a value of (mod_count =
0). If updating, then they must have a constant value for the update
path (a value that's greater than 0, naturally - say 2), which doesn't
make any sense in the context of an upsert's auxiliary update - what
happened to the 0 value? Sorry, but I don't think your example makes
sense - I can't see what would motivate anyone to write a query like
that with those RLS policies in place. It sounds like you're talking
about an insert and a separate update that may or may not affect the
same row, and not an upsert. Then those policies make sense, but in
practice they render the upsert you describe contradictory.

FWIW, I'm not suggesting that there couldn't possibly be a use case
that doesn't do well with this convention where we enforce RLS
deepening on the path taken. The cases are just very marginal, as I
think your difficulty in coming up with a convincing counter-argument
shows. I happen to think what Stephen and I favor ("bunching together"
USING() barrier quals and check options from INSERT and UPDATE
policies) is likely to be the best alternative available on balance.

More generally, you could point out that I'm actually testing
different tuples at different points in query processing under that
regime (e.g. the post-insert tuple, or the before-update conflicting,
existing tuple from the target, or the post update tuple) and so
things could fail when the update path is taken despite the fact that
they didn't fail when the insert path was taken. That's technically
true, of course, but with idiomatic usage it isn't true, and that's
what I care about.

Does anyone have another counter-example of a practical upsert
statement that cannot be used with certain RLS policies due to the
fact that we chose to "bunch together" INSERT and UPDATE RLS policies?
-- 
Peter Geoghegan