Re: MERGE Specification

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.

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.

> Now, I have changed the RULE strategy of MERGE to the better logic.
> And I am working on triggers for MERGE, which is also mentioned in the
> instruction file. I will build a new patch with no long comment and
> blank line around functions, and possibly contain the regress test
> file and this sgml instructions in it.
>
> I wish we can reach a agreement on the DO NOTHING thing before my next
> submission, so I can make necessary modification on my code for
> it. (the new patch may be finished in one or two days, I think)
>
> Thanks!
>
> PS: I have an embarrassing question: how to view the sgml instructions
> of postgres in web page form, rather than read the source code of
> them?

If you edit the files, as shown in the patches here, then you just need
to drop into the doc/sgml/src directory and type "make". The SGML will
then be compiled into HTML and you can view the resulting file directly
in your web browser.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services