Re: MERGE Specification

On Fri, 2008-04-25 at 09:10 -0400, Alvaro Herrera wrote:
> Simon Riggs wrote:
>
> > I'm now happy that we can get a spec-compliant end result by always
> > forcing NOT MATCHED rules to occur before MATCHED rules, when we have at
> > least one unique index.
>
> ... and raise an ERROR when there is no unique index?

No, I think an ERROR is not required, nor desirable.

In the absence of a unique index we allow exactly duplicate rows to
exist in a table. This is effectively user defined behaviour, albeit the
default setting.

We have two choices of behaviour:

1. If a MERGE statement runs and sees a row in the target table is NOT
MATCHED then it will insert a row. It is possible that a concurrent
MERGE statement could also see the row in the target table as NOT
MATCHED and then insert a duplicate row.

2. In the absence of a Unique Index, throw an ERROR because a concurrent
MERGE *might* result in duplicate Inserts. (i.e. prevent the above).

(1) is a situation possible with concurrent INSERTs into a table without
a unique index, so I see no reason to make MERGE follow (2) when INSERTs
do not.

Also, it is possible for a MERGE to generate duplicate rows in a table
if the INSERT clause contained constants for example. In the absence of
an applicable rule the MERGE will generate INSERT DEFAULT VALUES, i.e.
an all-constant insert will take place. So the MERGE spec allows the
inserting of duplicate rows without error.

We could include additional options to control this behaviour, if anyone
thinks it worthwhile, but ISTM more restrictive than protective.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com