> 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.brightwell@crunchydatasolutions.com
Database Engineer - www.crunchydatasolutions.com