Re: ask for review of MERGE

On Mon, Oct 18, 2010 at 9:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I think that MERGE is supposed to trigger one rule for each row in the
> source data. So:
>
> On Sun, Oct 17, 2010 at 8:20 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> > MERGE INTO Stock t
> > USING (SELECT * FROM Stock WHERE item_id=10) AS s
> > ON s.item_id=t.item_id
> > WHEN MATCHED THEN UPDATE SET balance=s.balance + 1
> > WHEN NOT MATCHED THEN INSERT VALUES (10,1)
> > ;
> >
> > This works fine, and updates the matching row:
> >
> > item_id | balance
> > ---------+---------
> > 20 | 1900
> > 10 | 2201
>
> Here you have one row of source data, and you got one action (the WHEN
> MATCHED case).
>
> > But if I give it a key that doesn't exist instead:
> >
> > MERGE INTO Stock t
> > USING (SELECT * FROM Stock WHERE item_id=30) AS s
> > ON s.item_id=t.item_id
> > WHEN MATCHED THEN UPDATE SET balance=s.balance + 1
> > WHEN NOT MATCHED THEN INSERT VALUES (30,1)
> > ;
> >
> > This doesn't execute the NOT MATCHED case and INSERT the way I expected
> it
> > to. It just gives back "MERGE 0".
>
> Here you have no rows of source data (the USING (SELECT ...) doesn't
> return anything, since no rows exist) so nothing happens.
>
>
Yes.
The MERGE process is based on a left join between the source table and
target table.
Since here the source table is empty, no join is carried, and thus no MERGE
action is taken.

But, is it correct logically? I mean, should we insert some rows in the
above example rather than do nothing?

> > Since I wasn't sure if the whole "subquery in the USING clause" case was
> > really implemented fully, I then tried to do this with something more
> like
> > the working regression test examples. I expected this to do the same
> thing
> > as the first example:
> >
> > MERGE INTO Stock t
> > USING Stock s
> > ON s.item_id=10 AND s.item_id=t.item_id
> > WHEN MATCHED THEN UPDATE SET balance=s.balance + 1
> > WHEN NOT MATCHED THEN INSERT VALUES (10,1)
> > ;
> >
> > But it gives back this:
> >
> > ERROR: duplicate key value violates unique constraint
> "stock_item_id_key"
> > DETAIL: Key (item_id)=(10) already exists.
>
> Here you have two rows of source data. The ON clause represents the
> join condition. The item_id=10 row matches - so you get an update,
> presumably, though we can't see that as things turn out - and the
> item_id=20 row doesn't match - so you try to insert (10, 1), which is
> a duplicate key, thus the error.
>
> > Can't tell from that whether it's hitting the MATCHED or NOT MATCHED side
> of
> > things to generate that. But it doesn't work any better if you give it
> an
> > example that doesn't exist:
> >
> > MERGE INTO Stock t
> > USING Stock s
> > ON s.item_id=30 AND s.item_id=t.item_id
> > WHEN MATCHED THEN UPDATE SET balance=s.balance + 1
> > WHEN NOT MATCHED THEN INSERT VALUES (30,1)
> > ;
> >
> > ERROR: duplicate key value violates unique constraint
> "stock_item_id_key"
> > DETAIL: Key (item_id)=(30) already exists.
>
> In this case neither row of the source data matches the join condition
> (s.item_id=30 might as well be constant FALSE as far as the test data
> is concerned) so you attempt to execute the NOT MATCHED side twice.
> So this one also looks correct to me.
>
>
Yes, that is what happened in the above two examples.

> The other thing I noticed that may take some work to sort out is that I
> haven't had any luck getting MERGE to execute from within a plpgsql
> function. I was hoping I could use this to update the pgbench tables:

Good catch. Considering the size of this patch, I have no problem
leaving this to the eventual committer to fix, or to a subsequent
commit.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company