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

On Sun, Nov 22, 2020 at 5:56 AM Peter Geoghegan wrote: > On Sat, Nov 21, 2020 at 12:58 AM Andy Fan > 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: >

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

On Sat, Nov 21, 2020 at 11:27 PM Pavel Stehule wrote: > > > so 21. 11. 2020 v 9:59 odesílatel Andy Fan > napsal: > >> Thank all of you for your great insight! >> >> On Sat, Nov 21, 2020 at 9:04 AM Peter Geoghegan wrote: >> >>> On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson >>> wrote: >>> > I

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

On Sat, Nov 21, 2020 at 12:58 AM Andy Fan 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 descript

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

so 21. 11. 2020 v 9:59 odesílatel Andy Fan napsal: > Thank all of you for your great insight! > > On Sat, Nov 21, 2020 at 9:04 AM Peter Geoghegan wrote: > >> On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson >> wrote: >> > I am sadly not familiar enough with Oracle or have access to any Oracle >

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

Thank all of you for your great insight! On Sat, Nov 21, 2020 at 9:04 AM Peter Geoghegan wrote: > On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson > 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

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

On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson 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 RE

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

On 11/20/20 3:25 PM, Andy Fan wrote:> On Fri, Nov 20, 2020 at 9:37 PM Andreas Karlsson > wrote: On 11/20/20 9:57 AM, Andy Fan wrote: > Thank you for your attention. Your suggestion would fix the issue.  However > The difference will cause some risks wh

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

Hi Andreas: Thanks for your input. On Fri, Nov 20, 2020 at 9:37 PM Andreas Karlsson wrote: > On 11/20/20 9:57 AM, Andy Fan wrote: > > Thank you for your attention. Your suggestion would fix the issue. > However > > The difference will cause some risks when users move their application > > from

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

On 11/20/20 9:57 AM, Andy Fan wrote: Thank you for your attention. Your suggestion would fix the issue.  However The difference will cause some risks when users move their application from Oracle to PostgreSQL. So I'd like to think which behavior is more reasonable. I think PostgreSQL's behav

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

On Thu, Nov 19, 2020 at 11:49 PM Tom Lane wrote: > Andy Fan writes: > > create table su (a int, b int); > > insert into su values(1, 1); > > > - session 1: > > begin; > > update su set b = 2 where b = 1; > > > - sess 2: > > select * from su where a in (select a from su where b = 1) for update; >

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

Andy Fan writes: > create table su (a int, b int); > insert into su values(1, 1); > - session 1: > begin; > update su set b = 2 where b = 1; > - sess 2: > select * from su where a in (select a from su where b = 1) for update; This'd probably work the way you expect if there were "for update" in

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

We can reproduce this difference with the following steps. create table su (a int, b int); insert into su values(1, 1); - session 1: begin; update su set b = 2 where b = 1; - sess 2: select * from su where a in (select a from su where b = 1) for update; - sess 1: commit; Then session 2 can get