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