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

From: Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Different results between PostgreSQL and Oracle for "for update" statement
Date: 2020-11-19 13:11:48
Message-ID: CAKU4AWq3rfjZQJBSvmbEsRp6ENArgMmzW4MHVDQ9UTic6p2pcA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 the result.

PostgreSQL:

a | b
---+---
1 | 2
(1 row)

Oracle: It gets 0 rows.

Oracle's plan is pretty similar to Postgres.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2828511618

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 4 (0)| 00:00:01 |
| 1 | FOR UPDATE | | | | | |
| 2 | BUFFER SORT | | | | | |
|* 3 | HASH JOIN SEMI | | 1 | 52 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| SU | 1 | 26 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| SU | 1 | 26 | 2 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------

Any thoughts on who is wrong?

--
Best Regards
Andy Fan

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2020-11-19 13:24:23 Re: [HACKERS] logical decoding of two-phase transactions
Previous Message Daniel Gustafsson 2020-11-19 13:05:35 Re: scram-sha-256 broken with FIPS and OpenSSL 1.0.2