Re: someone working to add merge?

On Fri, Nov 25, 2005 at 09:14:47AM -0500, Jan Wieck wrote:
> 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.

Yes, that's the way I read the standard and how I was thinking it could
be implemented. It does simplify the case suggested by people that want
atomic REPLACE, because you only have one statement to repeat until you
get success.

do
MERGE ...;
while( not error and modified_rows <> 1 )

I was thinking that we could make a seperate REPLACE command which can
only insert or update one row, but can do it atomically. Basically a
loop like above with builtin savepoints.

Alternativly, we could special case the MERGE-without-USING case, in
the cases where the plan simply devolves into an Index Scan, but I
don't like special casing. If we're going to have special semantics we
should have a seperate statement so it's clear that it's special.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)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.