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

On Sat, Nov 21, 2020 at 11:27 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> so 21. 11. 2020 v 9:59 odesílatel Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
> napsal:
>
>> 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.
>>
>
> 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