Re: [HACKERS] MERGE SQL Statement for PG11

Hi Stephen,

On Tue, Feb 6, 2018 at 3:37 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

>
>
> Coming out of that, my understanding is that Simon is planning to have a
> patch which implements RLS and partitioning (though the query plans for
> partitioning may be sub-par and not ideal) as part of MERGE and I've
> agreed to review at least the RLS bits (though my intention is to at
> least go through the rest of the patch as well, though likely in less
> detail). Of course, I encourage others to review it as well.
>
>
Thanks for volunteering to review the RLS bits. I am planning to send a
revised version soon. As I work through it, I am faced with some semantic
questions again. Would appreciate if you or anyone have answers to those.

While executing MERGE, for existing tuples in the target table, we may end
up doing an UPDATE or DELETE, depending on the WHEN MATCHED AND conditions.
So it seems unlikely that we would be able to push down USING security
quals down to the scan. For example, if the target row is set for deletion,
it seems wrong to exclude the row from the join based on UPDATE policy's
USING quals. So I am thinking that we should apply the respective USING
quals *after* the decision to either update, delete or do nothing for the
given target tuple is made.

The question I have is, if the USING qual evaluates to false or NULL,
should we silently ignore the tuple (like regular UPDATE does) or throw an
error (like INSERT ON CONFLICT DO UPDATE)? ISTM that we might have decided
to throw an error in case of INSERT ON CONFLICT to avoid any confusion
where the tuple is neither inserted nor updated. Similar situation may
arise with MERGE because for a source row, we may neither do UPDATE
(because of RLS) nor INSERT because a matching tuple already exists. But
someone may argue that we should stay closer to regular UPDATE/DELETE.
Apart from that, are there any security angles that we need to be mindful
of and would those impact the choice?

SELECT policies will be applied to the target table during the scan and
rows which do not pass SELECT quals will not be processed at all. If there
are NOT MATCHED actions, we might end up inserting duplicate rows in that
case or throw errors, but I don't see anything wrong with that. Similar
things would have happened if someone tried to insert rows into the table
using regular INSERT.

Similarly, INSERT policies will be applied when MERGE attempts to INSERT a
row into the table and error will be thrown if the row does not satisfy
INSERT policies.

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services