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