Re: ask for review of MERGE

On 2010-10-23 8:34 AM +0300, Greg Smith wrote:
> While the performance doesn't need to be great in V1, there needs to be
> at least some minimal protection against concurrency issues before this
> is commit quality.

What's been bothering me is that so far there has not been an agreement
on whether we need to protect against concurrency issues or not. In
fact, there has been almost no discussion about the concurrency issues
which AIUI have been the biggest single reason we don't have MERGE
already. Right now, this patch fails in even the simplest scenario:

=# create table foo(a int unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_a_key"
for table "foo"
CREATE TABLE

T1=# begin;
BEGIN
T1=# merge into foo using (values (1)) s(i) on s.i = foo.a when matched
then update set a=a+1 when not matched then insert values (s.i);
MERGE 1

T2=# merge into foo using (values (1)) s(i) on s.i = foo.a when matched
then update set a=a+1 when not matched then insert values (s.i);
-- blocks

T1=# commit;
COMMIT

.. and T2 gets a unique constraint violation.

As far as I'm concerned, the reason people want MERGE is to avoid these
problems; the nicer syntax is just a bonus. Having to LOCK the target
table makes this feature a lot less useful, even though there are a few
use cases where locking the table would be OK.

Regards,
Marko Tiikkaja