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.
