Re: someone working to add merge?

On 11/24/2005 1:30 AM, Martijn van Oosterhout wrote:

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

I guess you misunderstood. What I am talking about is a problem in the
order of execution. since we don't have predicate locking, there is a
possibility that our implementation of MERGE decides to do an INSERT
while another transaction does the same. What has to happen is that the
BEFORE INSERT trigger is called, then the heap tuple inserted, then the
index tuples created. At this time, the duplicate key error occurs,
telling us that we had a conflict and that we have to try an UPDATE
instead. That means, in the end this particular row's INSERT has never
happened and we have to undo the BEFORE INSERT triggers actions too.

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

Not following the semantics is an error. MERGE is not supposed to do
multiple inserts for the same match, concurrency or not.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #