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

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

but I am afraid so there is nothing about the different behaviour of
snapshots.

Regards

Pavel

> --
> Best Regards
> Andy Fan
>