Re: INSERT ... ON CONFLICT UPDATE and RLS

On 9 January 2015 at 08:49, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Fri, Jan 9, 2015 at 12:19 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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.
>

Whoa, hang on. I think you're being a bit quick to dismiss that
example. Why shouldn't I want an upsert where the majority of the
table columns follow the usual "make it so" pattern of an upsert, but
there is also this kind of audit column to be maintained? Then I would
write something like

INSERT INTO tbl (<some values>, 0)
ON CONFLICT UPDATE SET <same values>, mod_count=mod_count+1;

The root of the problem is the way that you're proposing to combine
the RLS policies (using AND), which runs contrary to the way RLS
policies are usually combined (using OR), which is why this kind of
example fails -- RLS policies in general aren't intended to all be
true simultaneously.

Regards,
Dean