On Wed, Nov 23, 2005 at 04:55:25PM -0500, Jan Wieck wrote:
> The largest problem I see with MERGE is the question of BEFORE triggers.
> Consider a BEFORE INSERT trigger that modifies a third table, after
> which the constraint or whatever post-heap_insert-attempt we might use
> detects a conflict. How do we undo the actions of the BEFORE trigger?
> The only way to do that is to plan the query as a nestloop, with the
> USING part as the outer loop. If the (updating) scan of the INTO
> relation did not hit any tuple, then do the INSERT. We can only undo the
> side effects of any BEFORE trigger by wrapping each and evey nested INTO
> relation insert attempt into its own subtransaction.
Umm, if there are any errors you abort the transaction, just like any
other case. ACID requires that either the whole statement is done, or
none. If a trigger causes the INSERT or UPDATE to fail you have no
choice but to abort the transaction.
Besides, someone posted an example on Oracle, they don't require an
index so I don't think we realistically can say that people need one.
If two concurrent MERGEs, which can't see eachothers output, both end
up INSERTing, that not an error unless the user has a UNIQUE
constraint, so the problem vanishes.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.