Re: someone working to add merge?

On 11/25/2005 7:14 AM, Martijn van Oosterhout wrote:
> On Thu, Nov 24, 2005 at 11:11:34AM -0500, Jan Wieck wrote:
>> I guess you misunderstood. [...]
> 
> But I'm not sure we're supposed to handle that case anyway. Oracle at
> least doesn't require an index on the table being merged. And if I look
> at it from a visibility view point, if someone else does an INSERT in
> another transaction, then MERGE cannot see it and thus it will INSERT
> too. This isn't an error.

Hmmm ... so you maintain that MERGE without an explicit LOCK TABLE, done 
by the user before performing the MERGE, can create duplicate rows (WRT 
the merge condition) and consequently raise a duplicate key error if 
there is a UNIQUE constraint.

If that is what the standard describes, then it can be implemented 
without any sort of index or constraint requirement. The query tree for 
MERGE will have the INTO relation as a left outer join. In the case of a 
match of this outer join, one set of targetlist expressions is used to 
form the result tuple containing the INTO-relations ctid. That result 
tuple us useable for heap_update() or heap_delete(). In the case of 
no-match another set of target list expressions is used, suitable for 
heap_insert(). This way, MERGE will work with one single sequential scan 
of the INTO relation in case there is no suitable index.


Jan

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