Horton

Thread

Commits

  1. Fix handling of updated tuples in the MERGE statement

  1. MERGE behavior with REPEATABLE READ isolation level

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-02-15T01:20:49Z

    Hi hackers,

I found it strange that ExecMergeMatched() checks for
IsolationUsesXactSnapshot() in the TM_Deleted case, but not in the
TM_Updated case.  Indeed, EPQ works on the repeatable read isolation level!

s1# create table test (id int primary key, val int);
s1# insert into test values (1,0);

s2# begin;
s2# update test set val = val + 100;

s1# MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc)
      ON t.id = s.id
    WHEN MATCHED THEN
      UPDATE SET val = t.val + s.inc
    WHEN NOT MATCHED THEN
      INSERT (id, val) VALUES (s.id, s.inc);
(waiting ...)

s2# commit;
s1# MERGE 1
s1# select * from test;
 id | val
----+-----
  1 | 200
(1 row)

Quick search didn't give an explanation of this to me.  I also don't see
this covered by an isolation test.  Is it an intended behavior or a bug?
(Sorry for buzz if this was discussed before)

------
Regards,
Alexander Korotkov
Supabase

  2. Re: MERGE behavior with REPEATABLE READ isolation level

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-02-15T01:22:43Z

    On Sun, Feb 15, 2026 at 3:20 AM Alexander Korotkov <aekorotkov@gmail.com>
wrote:

> Hi hackers,
>
> I found it strange that ExecMergeMatched() checks for
> IsolationUsesXactSnapshot() in the TM_Deleted case, but not in the
> TM_Updated case.  Indeed, EPQ works on the repeatable read isolation level!
>
> s1# create table test (id int primary key, val int);
> s1# insert into test values (1,0);
>
> s2# begin;
> s2# update test set val = val + 100;
>
> s1# MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc)
>       ON t.id = s.id
>     WHEN MATCHED THEN
>       UPDATE SET val = t.val + s.inc
>     WHEN NOT MATCHED THEN
>       INSERT (id, val) VALUES (s.id, s.inc);
> (waiting ...)
>
> s2# commit;
> s1# MERGE 1
> s1# select * from test;
>  id | val
> ----+-----
>   1 | 200
> (1 row)
>

Oh, sorry I missed the begin statement for s1.  The complete case should
look like this.

s1# create table test (id int primary key, val int);
s1# insert into test values (1,0);

  s2# begin;
  s2# update test set val = val + 100;

s1# begin isolation level repeatable read;
s1# MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc)
      ON t.id = s.id
    WHEN MATCHED THEN
      UPDATE SET val = t.val + s.inc
    WHEN NOT MATCHED THEN
      INSERT (id, val) VALUES (s.id, s.inc);
(waiting ...)

  s2# commit;

s1# MERGE 1
s1# select * from test;
 id | val
----+-----
  1 | 200
(1 row)

------
Regards,
Alexander Korotkov
Supabase

  3. Re: MERGE behavior with REPEATABLE READ isolation level

    Tender Wang <tndrwang@gmail.com> — 2026-02-24T03:12:20Z

    Hi Alexander,

Alexander Korotkov <aekorotkov@gmail.com> 于2026年2月15日周日 09:23写道：
> Oh, sorry I missed the begin statement for s1.  The complete case should look like this.
>
> s1# create table test (id int primary key, val int);
> s1# insert into test values (1,0);
>
>   s2# begin;
>   s2# update test set val = val + 100;
>
> s1# begin isolation level repeatable read;
> s1# MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc)
>       ON t.id = s.id
>     WHEN MATCHED THEN
>       UPDATE SET val = t.val + s.inc
>     WHEN NOT MATCHED THEN
>       INSERT (id, val) VALUES (s.id, s.inc);
> (waiting ...)
>
>   s2# commit;
>
> s1# MERGE 1
> s1# select * from test;
>  id | val
> ----+-----
>   1 | 200
> (1 row)
>

I tried "update test set val = val + 100;" but the SQL reported a
"could not serialize access due to concurrent update" error.
It seems that the MERGE command should behave identically to UPDATE
when performing a match action.

I wrote a fix patch and attached it, and added your test case, too.


-- 
Thanks,
Tender Wang

  4. Re: MERGE behavior with REPEATABLE READ isolation level

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-02-24T09:09:54Z

    On Tue, 24 Feb 2026 at 03:13, Tender Wang <tndrwang@gmail.com> wrote:
>
> Alexander Korotkov <aekorotkov@gmail.com> 于2026年2月15日周日 09:23写道：
> >
> > I found it strange that ExecMergeMatched() checks for IsolationUsesXactSnapshot() in the TM_Deleted case, but not in the TM_Updated case.
>
> I tried "update test set val = val + 100;" but the SQL reported a
> "could not serialize access due to concurrent update" error.
> It seems that the MERGE command should behave identically to UPDATE
> when performing a match action.
>

Yes, I agree. I think this is a bug (probably just an oversight in the
original MERGE commit).

Regards,
Dean

  5. Re: MERGE behavior with REPEATABLE READ isolation level

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-02-26T17:03:01Z

    On Tue, Feb 24, 2026 at 5:13 AM Tender Wang <tndrwang@gmail.com> wrote:
> Alexander Korotkov <aekorotkov@gmail.com> 于2026年2月15日周日 09:23写道：
> > Oh, sorry I missed the begin statement for s1.  The complete case should look like this.
> >
> > s1# create table test (id int primary key, val int);
> > s1# insert into test values (1,0);
> >
> >   s2# begin;
> >   s2# update test set val = val + 100;
> >
> > s1# begin isolation level repeatable read;
> > s1# MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc)
> >       ON t.id = s.id
> >     WHEN MATCHED THEN
> >       UPDATE SET val = t.val + s.inc
> >     WHEN NOT MATCHED THEN
> >       INSERT (id, val) VALUES (s.id, s.inc);
> > (waiting ...)
> >
> >   s2# commit;
> >
> > s1# MERGE 1
> > s1# select * from test;
> >  id | val
> > ----+-----
> >   1 | 200
> > (1 row)
> >
>
> I tried "update test set val = val + 100;" but the SQL reported a
> "could not serialize access due to concurrent update" error.
> It seems that the MERGE command should behave identically to UPDATE
> when performing a match action.
>
> I wrote a fix patch and attached it, and added your test case, too.

Thank you for the confirmation and for the patch.  Regarding the test
case, can we handle this without introducing a new .spec file?  I
think we can add 1-2 permutations to merge-update.spec.  Even existing
step should work, you only need one new which begins transaction in RR
isolation mode.

------
Regards,
Alexander Korotkov
Supabase

  6. Re: MERGE behavior with REPEATABLE READ isolation level

    Tender Wang <tndrwang@gmail.com> — 2026-02-27T12:54:06Z

    Hi all,

Alexander Korotkov <aekorotkov@gmail.com> 于2026年2月27日周五 01:03写道：
> Thank you for the confirmation and for the patch.  Regarding the test
> case, can we handle this without introducing a new .spec file?  I
> think we can add 1-2 permutations to merge-update.spec.  Even existing
> step should work, you only need one new which begins transaction in RR
> isolation mode.

Done
Please see the v2 patch.

-- 
Thanks,
Tender Wang

  7. Re: MERGE behavior with REPEATABLE READ isolation level

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-03-01T22:32:12Z

    On Fri, Feb 27, 2026 at 2:54 PM Tender Wang <tndrwang@gmail.com> wrote:
> Alexander Korotkov <aekorotkov@gmail.com> 于2026年2月27日周五 01:03写道：
> > Thank you for the confirmation and for the patch.  Regarding the test
> > case, can we handle this without introducing a new .spec file?  I
> > think we can add 1-2 permutations to merge-update.spec.  Even existing
> > step should work, you only need one new which begins transaction in RR
> > isolation mode.
>
> Done
> Please see the v2 patch.

Looks good to me.  I'll push it if no objections.

------
Regards,
Alexander Korotkov
Supabase