On Tue, May 17, 2022 at 4:57 AM alias <postgres.rocks@xxxxxxxxx> wrote:
My thought process:update = delete + insert.so create policy emp_upd on emp for update to public using (true); should be ok for updating every row, let insert policy handle new row.since there is only one check_expression, also no need to worry about permissive/restrictive.but it seems, I need to create the same policy as insert operation to update operation to validate the new content/row of update operation.
update = delete + insert is not universally true. Its main point is that due to MVCC when you update something the old tuple is deleted (but remains around waiting to be vacuumed) and a new tuple is created. It is also an implementation detail - while the usage of INSERT/UPDATE/DELETE in CREATE POLICY are semantically significant and mean to convey the user writing out those specific commands (or subcommand in the case of the ON CONFLICT UPDATE subclause of INSERT).
In this case it gives the policy writer flexibility, at the cost of some duplication. One useful thing to do is write a function that accepts either columns, or the table's data type, as an input argument and put the logic in there. Then just call the function in the policy with check and/or using clauses.
David J.
