The DBA in me is having a difficult time understanding PostgreSQL's behavior with regard to the delete/insert within a transaction?
In MySQL, I get the exact behavior that I expect with anything above RC isolation level (which is the default for PostgreSQL):
session 1> create table foo ( k varchar(10) not null, v varchar(10) not null ) ;
session 1> set transaction isolation level read committed;
session 2> set transaction isolation level read committed;
session 1> insert into foo (k,v) values ('a','b');
session 1> begin;
session 2> begin;
session 1> delete from foo where k = 'a';
session 2> delete from foo where k = 'a';
-- transaction hangs here as expected since session 1 has a lock on this record
session 1> insert into foo (k,v) values ('a','c');
session 1> commit;
-- session 2 executes the delete
session 2> insert into foo (k,v) values ('a','e');
session 2> commit;
session 2> select * from foo;
+---+---+
| k | v |
+---+---+
| a | e |
+---+---+
When I do this in PostgreSQL, I end up with 2 rows in the table, which seems fundamentally incorrect and to me implies that PostgreSQL's implementation of read committed is flawed and should be fixed. The transaction in session 2, at RC isolation, should see the most recently committed result, which is the single record from session 1. I'm not CJ Date, though so am very likely missing some subtlety.PostgreSQL after the sequence above:
test=# select * from foo;
k | v
---+---
a | c
a | e
(2 rows)
What am I missing? Why is this considered correct behavior?