> I'm not convinced this is the right place, but at a minimum it should be > referenced from the RLS documentation. Further, it should be noted that > users who have direct SQL access can control what the isolation level > is for their transaction.
I agree that it should be referenced by the RLS docs. However, I'm not sure I can think of a better place for it. My reasons for choosing this location was that the behavior seems specific to the READ COMMITTED isolation level and was an accepted MVCC anomaly; not necessarily specific only to RLS and SBV. But, again, I'd agree that referencing it in the other locations would be desired. Of course, I'm willing to accept that I may be making the wrong assumptions. > Also, isn't it possible to avoid this by locking the records? If the > locking fails or blocks then you know another user has those records > locked and you don't update or you wait until you hold the lock. > Assuming that works (I don't immediately see why it wouldn't..), we > should provide an example. I haven't found that to work, at least not with the specific case presented by Peter. Based on the following (output from Peter's isolation test), I would understand that the 'malicious' UPDATE is waiting for the previous update to be committed before it continues, even without the FOR UPDATE lock on the rows. step no_trust_mallory: update users set group_id = 1 where user_name = 'mallory'; step update_hc: update information set info = 'secret' where group_id = 2; step updatem: update information set info = info where group_id = 2 returning 'mallory update: ' m, *; <waiting ...> step commit_hc: commit; step updatem: <... completed> As well, due to this, as described by the READ COMMITTED documentation: "it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update" I'm not convinced that this is something that FOR UPDATE can address for this specific case. If inconsistencies in the 'snapshot' can be expected and are accepted at this isolation level, then I'm not sure how we can reasonably expect locking the rows to have any affect. Though, again, I'm willing to accept that I am not fully understanding this behavior and that I am completely wrong. -Adam -- Adam Brightwell - adam.brightw...@crunchydatasolutions.com Database Engineer - www.crunchydatasolutions.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers