Re: ask for review of MERGE
От | Boxuan Zhai |
---|---|
Тема | Re: ask for review of MERGE |
Дата | |
Msg-id | AANLkTikB+=d0Ns2sCc1H-qP_2fZDNJ_ZwA3MQoYNbySY@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: ask for review of MERGE (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: ask for review of MERGE
|
Список | pgsql-hackers |
On Mon, Oct 18, 2010 at 9:54 PM, Robert Haas <robertmhaas@gmail.com> wrote:
leaving this to the eventual committer to fix, or to a subsequent
commit.
--
The Enterprise PostgreSQL Company
I think that MERGE is supposed to trigger one rule for each row in the
source data. So:Here you have one row of source data, and you got one action (the WHEN
On Sun, Oct 17, 2010 at 8:20 PM, Greg Smith <greg@2ndquadrant.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
MATCHED case).Here you have no rows of source data (the USING (SELECT ...) doesn't
> 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".
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 wasHere you have two rows of source data. The ON clause represents the
> 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.
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.In this case neither row of the source data matches the join condition
> 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.
(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> 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:
leaving this to the eventual committer to fix, or to a subsequent
commit.
--
The Enterprise PostgreSQL Company
В списке pgsql-hackers по дате отправления:
Предыдущее
От: David FetterДата:
Сообщение: Re: How to determine failed connection attempt due to invalid authorization (libpq)?