Re: MERGE Specification

On Thu, 2010-08-05 at 18:17 +0300, Heikki Linnakangas wrote:
> 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.

err...

* DELETE is an extension to the standard, though supported by Oracle,
DB2 and SQLServer and damn useful

* INSERT DEFAULT VALUES is an extension to the standard, though matches
options on the normal INSERT clause

* rule support is an extension to the standard

* It appears we would be in violation of the standard on
14.12 General Rule 6 a) i) 2) B), p.890
(Oh, I wish I was joking, there really is such a paragraph number)
which specifies that the join between source and target table must not
return multiple rows or must return "cardinality violation". That's
pretty difficult thing to check and not very useful if it does do that.

anyway, that list isn't an argument in favour of change. The argument in
favour of a fail-safe default is that it is a safe coding practice that
the PostgreSQL project already uses itself. The only way to write a safe
MERGE SQL statement is with an extension to the standard...

Principle of minimal extension would mean we only need to support RAISE
ERROR, to allow people to specify they actively want statement to fail
if the list of WHEN clauses does not produce a match.

> > 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.

I checked the standard before commenting previously and have done so
again here. I can't see anything that refers to this (in SQL:2008),
either way.

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