so 21. 11. 2020 v 9:59 odesÃlatel Andy Fan <zhihui.fan1...@gmail.com> napsal:
> Thank all of you for your great insight! > > On Sat, Nov 21, 2020 at 9:04 AM Peter Geoghegan <p...@bowt.ie> wrote: > >> On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson <andr...@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. > yes, it would be nice to be better documented, somewhere - it should not be part of Postgres documentation. Unfortunately, people who know Postgres perfectly do not have the same knowledge about Oracle. Some differences are documented in Orafce documentation https://github.com/orafce/orafce/tree/master/doc but I am afraid so there is nothing about the different behaviour of snapshots. Regards Pavel > -- > Best Regards > Andy Fan >
