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

On Sat, Nov 21, 2020 at 12:58 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> 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.

Actually, it is documented here:
https://www.postgresql.org/docs/devel/transaction-iso.html

The description starts with: "UPDATE, DELETE, SELECT FOR UPDATE, and
SELECT FOR SHARE commands behave the same as SELECT in terms of
searching for target rows...".

I imagine that the number of application developers that are aware of
this specific aspect of transaction isolation in PostgreSQL (READ
COMMITTED conflict handling/EvalPlanQual()) is extremely small. In
practice it doesn't come up that often. Though Postgres hackers tend
to think about it a lot because it is hard to maintain.

I'm not saying that that's good or bad. Just that that has been my experience.

I am sure that some application developers really do understand the
single most important thing about READ COMMITTED mode's behavior: each
new command gets its own MVCC snapshot. But I believe that Oracle is
no different. So in practice application developers probably don't
notice any difference between READ COMMITTED mode in practically all
cases. (Again, just my opinion.)

--
Peter Geoghegan