I have this table:
CREATE TABLE test (id INT PRIMARY KEY);
INSERT INTO test VALUES (1);
Then I run these two transactions simultaneously:
one | two
-------------------------------+-------------------------------
BEGIN; |
| BEGIN;
DELETE FROM test; --DELETE 1 |
| SELECT * FROM test FOR UPDATE; -- Blocks...
INSERT INTO test VALUES (1); |
COMMIT; |
| -- ...returns 0 rows
How is it possible that the select in transaction two returns 0 rows? There was
never a transaction that committed with 0 rows in test. Shouldn't read
committed isolation prevent this?
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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
