Chapter 7 Transaction behavior
Most of the transaction behavior are exactly same, however the below stuff is not.
7.1 Handled Statement Failure.
create table t (a int primary key, b int);
begin;
insert into t values(1,1);
insert into t values(1, 1);
commit;
Oracle : commit can succeed. t has 1 row after that.
PostgreSQL: commit failed due to the 2nd insert failed. so t has 0 row.
7.2 DML with Subquery
Case 1:
create table dml(a int, b int);
insert into dml values(1, 1), (2,2);
-- session 1:
begin;
delete from dml where a in (select min(a) from dml);
--session 2:
delete from dml where a in (select min(a) from dml);
-- session 1:
commit;
In Oracle: 1 row deleted in sess 2. so 0 rows in the dml at last.
In PG : 0 rows are deleted in sess 2, so 1 rows in the dml at last.
Oracle probably detects the min(a) is changed and rollback/rerun the statement.
The same reason can cause the below difference as well.
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;
In oracle, 0 row is selected. In PostgreSQL, 1 row (1, 2) is selected.
A best practice would be never use subquery in DML & SLEECT … FOR UPDATE. Even in Oracle, the behavior is inconsistent as well. Oracle between 11.2.0.1 and 11.2.0.3 probably behavior same as Postgres, but other versions not.