I think this paragraph explains why it happens:
https://www.postgresql.org/docs/9.6/static/transaction- .iso.html#XACT-READ-COMMITTED
> If the first updater commits, the second updater will ignore the row if the
> first updater deleted it
How is that allowed in READ COMMITTED? I never committed with 0 rows in test,
so I expected to never have a SELECT that returns 0 rows.
"... they will only find target rows that were committed as of the command start time"
The newly added test row from <one> did not exist when <two> began so it can never been seen by <two>. Period.
"In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress)"
The <two> SELECT * FROM test FOR UPDATE; query sees every single row in test but it indeed must wait due to the FOR UPDATE
"If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row."
Once <one> commits then <two> continues and ignores the now deleted rows.
Thus:
So, while there was never a time when the table contained zero rows all of the rows that <two> wanted to see were gone by the time it got to them and so it had nothing to show. This is what is documented and what you show.
In short, this behavior is the price you pay for not having to concern yourself with serialization errors - which is exactly what you would get if you executed these transactions in any of the higher isolation levels.
David J.
