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(at)Yahoo(dot)com #