Re: CREATE POLICY and RETURNING - Mailing list pgsql-hackers
| From | Zhaomo Yang |
|---|---|
| Subject | Re: CREATE POLICY and RETURNING |
| Date | |
| Msg-id | CALPr3oz+OTDvo7GU_QAT4urJW3UYzfEAPZ76uSuS6gAuUeVbwA@mail.gmail.com Whole thread Raw |
| In response to | Re: CREATE POLICY and RETURNING (Stephen Frost <sfrost@snowman.net>) |
| Responses |
Re: CREATE POLICY and RETURNING
(Stephen Frost <sfrost@snowman.net>)
|
| List | pgsql-hackers |
Stephen,
If no NEW or OLD is used, what happens? Or would you have
to always specify OLD/NEW for UPDATE, and then what about for the other
policies, and the FOR ALL policies?
I should be clearer with references to OLD/NEW. SELECT Predicates cannot reference any of them.
INSERT predicates cannot refer to OLD and DELETE predicates cannot refer to NEW. Basically,
for INSERT/UPDATE/DELETE, we specify predicates the same way as we do for triggers' WHEN condition.
As for FOR ALL, I think we will abandon it if we apply SELECT policy to other commands, since SELECT predicate
will be the new universally applicable read policy, which makes the FOR ALL USING clause much less useful. Of course users may need to specify separate predicates for different commands, but I think it is fine. How often do users want the same predicate for all the commands?
This could be accomplished with "USING (bar > 1)" and "WITH CHECK (foo > 1)", no?
Your sentence above that "USING and WITH CHECK are combined by AND"
isn't correct either- they're independent and are therefore really OR'd.
If they were AND'd then the new record would have to pass both USING and
WITH CHECK policies.
No, it is impossible with the current implementation.
CREATE TABLE test {
id int,
v1 int,
v2 int
};
Suppose that the user wants an update policy which is OLD.v1 > 10 OR NEW.v2 < 10.
As you suggested, we use the following policy
CREATE update_p ON test
FOR UPDATE TO test_user
USING v1 > 10
WITH CHECK v2 < 10;
(1) Assume there is only one row in the table
id | v1 | v2 |
1 | 11 | 20 |
Now we execute UPDATE test SET v2 = 100.
this query is allowed by the policy and the only row should be updated since v1's old value > 10, but will trigger an error because it violates the WITH CHECK clause.
(2) Again assume there is only one row in the table
id | v1 | v2 |
1 | 9 | 20 |
Now we execute UPDATE test SET v2 = 7.
this query is allowed by the policy and the only row should be updated since v2's new value < 10, nothing will be updated because the only row will be filtered out before update happens.
This is why I said USING and WITH CHECK are combined by AND. In order to update an row, first the row needs to be visible, which meaning it needs to pass the USING check, then it needs to pass the WITH CHECK.
Further, I'm not sure that I see how this would work in a case where you
have the SELECT policy (which clearly could only refer to OLD) applied
first, as you suggest?
We use SELECT policy to filter the table when we scan it (just like how we use USING clause now). The predicate of UPDATE will be checked later (probably similar to how we handle trigger's WHEN clause which can also reference OLD and NEW).
Thanks,
Zhaomo
pgsql-hackers by date: