On Sat, Nov 21, 2020 at 11:27 PM Pavel Stehule <pavel.steh...@gmail.com> wrote:
> > > 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 > > orafce project is awesome! > but I am afraid so there is nothing about the different behaviour of > snapshots. > > https://github.com/orafce/orafce/pull/120 is opened for this. -- Best Regards Andy Fan
