Re: [HACKERS] MERGE SQL Statement for PG11 - Mailing list pgsql-hackers
| From | Pavan Deolasee |
|---|---|
| Subject | Re: [HACKERS] MERGE SQL Statement for PG11 |
| Date | |
| Msg-id | CABOikdO-swZ55jfySLsmnO6h-0TX1Ynt7uXiek7gsrAbQB4SDA@mail.gmail.com Whole thread Raw |
| In response to | Re: [HACKERS] MERGE SQL Statement for PG11 (Stephen Frost <sfrost@snowman.net>) |
| Responses |
Re: [HACKERS] MERGE SQL Statement for PG11
|
| List | pgsql-hackers |
Hi Stephen,
On Tue, Feb 6, 2018 at 3:37 PM, Stephen Frost <sfrost@snowman.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.
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
PostgreSQL Development, 24x7 Support, Training & Services
pgsql-hackers by date: