On Tue, 5 Mar 2024 at 13:55, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> > If I only execute merge , I will get the following error:
> > merge into tgt a using src1 c on a.a = c.a when matched then update set b = c.b when not matched then insert
(a,b)values(c.a,c.b); -- excute fail
> > ERROR: MERGE command cannot affect row a second time
> > HIINT: Ensure that not more than one source row matches any one target row.
> >
> > But when I execute the update and merge concurrently, I will get the following result set.
>
> Yes, this should still produce that error, even after a concurrent update.
>
> My initial reaction is that neither of those blocks of code is
> entirely correct, and that they should both be doing both of those
> checks. I.e., something like the attached (which probably needs some
> additional test cases).
>
OK, I've pushed and back-patched that fix for this issue, after adding
some tests (nice catch, by the way!).
That wasn't related to the original issue though, so the problem with
UNION ALL still remains to be fixed. The patch from [1] looks
promising (for HEAD at least), but it really needs more pairs of eyes
on it (bearing in mind that it's just a rough proof-of-concept patch
at this stage).
[1] https://www.postgresql.org/message-id/CAEZATCVa-mgPuOdgd8%2BYVgOJ4wgJuhT2mJznbj_tmsGAP8hHJw%40mail.gmail.com
Regards,
Dean