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. #
#================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
