Re: MERGE vs REPLACE

Simon,

> The UPSERT concept is also supported by Teradata, who simply append an
> ELSE INSERT clause onto the standard UPDATE syntax. MySQL REPLACE seems
> to me to be a fairly small subset of MERGE functionality and we ought to
> be able to offer that functionality as a side branch of the main work.

Yes, I guess my hesitation on the full-table-lock strategy is that it
doesn't really fulfill the mandate for why people want REPLACE-like
statements ... to give them an INSERT-or-UPDATE with *higher* efficiency
and concurrency than doing two statements. That being said, I've
personally designed more than a dozen web applications and have not yet
been faced with a single circumstance of not knowing whether I wanted to
INSERT or UPDATE. I've even ported MySQL apps and found it easy to
re-code them to do "if $id = 0, then insert ..." without even needing to
use a pl/pgsql hack.

So we thus have two seperate use cases. The first, for bulk loading/ETL is
what MERGE fulfills rather neatly and for that full table locking is
perfectly OK, even desirable. You really don't want to MERGE-load the
same table on two threads at once.

The second case is for applications coded for MySQL; this is the REPLACE
case. However, the most common MySQL applications doing this use full
table locking (MyISAM) anyway! So, while full table locking wouldn't gain
them any performance over using two statements, it shouldn't lose them
anything they're used to having.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco