Re: Different results between PostgreSQL and Oracle for "for update" statement

Thank all of you for your great insight!

On Sat, Nov 21, 2020 at 9:04 AM Peter Geoghegan <pg@bowt.ie> wrote:

On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson <andreas@proxel.se> wrote:
> I am sadly not familiar enough with Oracle or have access to any Oracle
> license so I cannot comment on how Oracle have implemented their behvior
> or what tradeoffs they have made.

I bet that Oracle does a statement-level rollback for READ COMMITTED
mode's conflict handling.

I'd agree with you about this point,  this difference can cause more different

behavior between Postgres & Oracle (not just select .. for update). 

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. 

In PG: 0 rows are deleted. 

 

I'm not sure if this means that it locks multiple rows or not.

This is something not really exists and you can ignore this part:)  

 

About the statement level rollback,  Another difference is related. 

 

create table t (a int primary key, b int);

begin;

insert into t values(1,1);

insert into t values(1, 1);

commit; 

Oracle : t has 1 row, PG:  t has 0 row (since the whole transaction is

aborted).

I don't mean we need to be the same as Oracle, but to support a 

customer who comes from Oracle, it would be good to know the 

difference. 

--

Best Regards

Andy Fan