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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers