Re: MERGE Specification

On 05/08/10 17:22, Simon Riggs wrote:
> On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote:
>
>> In the contrary, Simon's instruction says that the DEFAULT action for
>> the tuple caught by no actions is
>> WHEN NOT MATCHED THEN INSERT DEFAULT VALUES
>>
>> From the user's point of view, these two kinds of MERGE command may
>> have not much differences. But, as the coder, I prefer current
>> setting, because we can save the implementation for a new type
>> of MERGE actions (DO NOTHING is a special merge action type). And,
>> thus, no checks and special process for it. (For example, we need to
>> make sure that DO NOTHING is the last WHEN clause, and it has no
>> additional qual. And we have to generate a INSERT DEFAULT VALUES
>> action for the MERGE command if we don't find the DO NOTHING action)
>>
>> Well, if people want the DO NOTHING action, I will add it in the
>> system.
>
> This is only important when using AND<search condition>, so its not
> important for the common UPSERT case of unconditional UPDATE/INSERT.

Assuming the default action if no other action matches is to do nothing,
then an explicit DO NOTHING is just a convenience. You can have the same
effect by having an "AND NOT <condition>" to all the actions following
the DO NOTHING action. I admit it's quite handy, but let's avoid
PostgreSQL extensions at this point.

> Personally, I would prefer the default action to be RAISE ERROR or
> similar. Otherwise its just too easy to get complex logic wrong and lose
> a few rows without noticing. If that was the case then you would
> definitely need DO NOTHING when you explicitly wanted to lose a few
> rows.
>
> You may think that's a bit strong, but consider that PostgreSQL uses
> default => ERROR in vast majority of switch() statements. I think its a
> safe coding practice and the annoyance of having run-time errors is much
> better than losing rows.
>
> The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not
> part of the standard AFAICS.

What does the standard say about this? We should follow the standard, I
don't see enough reason to deviate here.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com