3 ms·
This violates the promise of read committed. In RC, all transactions are supposed to see the most recently committed version of a record. The fact that it was
by AaronBBrown 13y ago
This violates the promise of read committed. In RC, all transactions are supposed to see the most recently committed version of a record. The fact that it was a delete, then an insert is an implementation detail and "not my problem" as a DBA. At the time the transaction in session 1 commits, the most recently committed version of k='a' is v='c'. PostgreSQL, in the situation, seems to be operating in read uncommitted isolation level...session 2 is treating the uncommitted delete in session 1 as the most recent version.
I'd like to give PostgreSQL the benefit of the doubt here, but this behavior seems fundamentally incorrect to me.
- skybrian 13y agoThat's the subtle part: they aren't two versions of the same record. They're two entirely different records, so all bets are off. (In your example, they don't even have the same primary key because no primary key is declared. PostgresSQL doesn't know that k is supposed to be a key; it's just another column.)
- AaronBBrown 13y agoThat is an implementation problem. The behavior is still incorrect. MySQL manages to do this correctly, so it's clearly not impossible. I can't come up with any other conclusion except that transaction isolation in pg is fundamentally broken when I see this type of case show up.