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(at)bowt(dot)ie> wrote:

> On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson <andreas(at)proxel(dot)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