I was reviewing the Concurrency Control chapter to work up suggestions for the guy who has agreed to create a doc patch to go with the Serializable Snapshot Isolation (SSI) patch. It occurred to me that there is a gap which has nothing to do with the patch, and wondered whether we should ignore it, include it along with the rest, or submit it as a separate patch.
The issue is that I don't see anything in the documentation which would lead people to expect the following behavior, and I'm inclined to think that our documentation should cover it somehow:
-- connection 1 test=# create table t (id int not null primary key, val int not null, matches bool not null); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE test=# insert into t select n, n, false from (select generate_series(1,10)) x(n); INSERT 0 10 test=# begin; BEGIN test=# update t set val = val + 1; UPDATE 10
-- connection 2 test=# update t set matches = true where val between 4 and 6; [blocks]
-- connection 1 test=# commit; COMMIT
-- connection 2 UPDATE 2 test=# select * from t; id | val | matches ----+-----+--------- 1 | 2 | f 2 | 3 | f 3 | 4 | f 6 | 7 | f 7 | 8 | f 8 | 9 | f 9 | 10 | f 10 | 11 | f 4 | 5 | t 5 | 6 | t (10 rows)
Note that the explicit BEGIN and COMMIT would not be necessary for this to occur -- it's just an easy way to get the timings right for an example of what can happen. The two update statements by themselves could do this if the timing happened to fall just the right way.
I think most people, having read the PostgreSQL documentation, would expect connection 2 to update three rows, since the table has three matching rows both before and after the transaction on connection 1.
This happens because under PostgreSQL's READ COMMITTED transaction isolation level, if a query blocks with a write conflict and the other transaction commits, the blocked transaction follows the pointer to the new version of the row and checks whether it still meets the criteria -- if so it operates on the new row; otherwise it ignores the row.
I'm not looking to change this -- the questions are whether the Concurrency Control chapter of the documentation should mention it and, if so, whether that should be submitted as a separate patch.