Re: BUG #19355: Attempt to insert data unexpectedly during concurrent update
| От | Bh W |
|---|---|
| Тема | Re: BUG #19355: Attempt to insert data unexpectedly during concurrent update |
| Дата | |
| Msg-id | CANjBVm3AMm=9-mB7=uqJjaWN-+RrA8+w-gXhZn6L7VHu7K_Urw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: BUG #19355: Attempt to insert data unexpectedly during concurrent update (Laurenz Albe <laurenz.albe@cybertec.at>) |
| Ответы |
Re: BUG #19355: Attempt to insert data unexpectedly during concurrent update
|
| Список | pgsql-bugs |
The issue is that the MERGE INTO match condition is not updated.
In the MATCHED path of MERGE INTO, when the target row satisfies the match condition and the condition itself has not changed, the system should still be able to handle concurrent updates to the same target row by relying on EvalPlanQual (EPQ) to refetch the latest version of the tuple, and then proceed with the intended update.
However, in the current implementation, even though the concurrent update does not modify any columns relevant to the ON condition, the EPQ recheck unexpectedly results in a match condition failure, causing the update path that should remain MATCHED to be treated as NOT MATCHED.
In the scenario shown above, if no primary key exists, an extra row will be inserted.
Further investigation shows that execution plans using Hash Join do not exhibit this problem.
However, in the current implementation, even though the concurrent update does not modify any columns relevant to the ON condition, the EPQ recheck unexpectedly results in a match condition failure, causing the update path that should remain MATCHED to be treated as NOT MATCHED.
In the scenario shown above, if no primary key exists, an extra row will be inserted.
Further investigation shows that execution plans using Hash Join do not exhibit this problem.
Could you please take a look at my explanation and let me know if anything is inaccurate? I’d also appreciate it if you could check the test scenario I provided. Thanks a lot!
Laurenz Albe <laurenz.albe@cybertec.at> 于2025年12月15日周一 14:25写道：
On Mon, 2025-12-15 at 01:40 +0000, PG Bug reporting form wrote:
> Start two transaction and update on same tuple, raise concurrent update and
> evalplanqual. It will be found out that the session with evalplanqual did
> not successfully update the data, but instead attempted to insert a row of
> data incorrectly.
I'd say that is expected.
If you need a guarantee that either INSERT or UPDATE succeed, you have to use
INSERT ... ON CONFLICT ... DO UPDATE
Yours,
Laurenz Albe
В списке pgsql-bugs по дате отправления: