Re: CREATE POLICY and RETURNING

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