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.
-Kevin