We can reproduce this difference with the following steps.
create table su (a int, b int);
insert into su values(1, 1);
- session 1:
begin;
update su set b = 2 where b = 1;
- sess 2:
select * from su where a in (select a from su where b = 1) for update;
- sess 1:
commit;
Then session 2 can get the result.
PostgreSQL:
a | b
---+---
1 | 2
(1 row)
Oracle: It gets 0 rows.
Oracle's plan is pretty similar to Postgres.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2828511618
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 4 (0)| 00:00:01 |
| 1 | FOR UPDATE | | | | | |
| 2 | BUFFER SORT | | | | | |
|* 3 | HASH JOIN SEMI | | 1 | 52 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| SU | 1 | 26 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| SU | 1 | 26 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
Any thoughts on who is wrong?
--