Re: UPSERT wiki page, and SQL MERGE syntax

On Fri, Oct 10, 2014 at 2:29 PM, Peter Geoghegan <pg@heroku.com> wrote:
> People keep remarking that they don't like that you can (optionally)
> name a unique index explicitly, and I keep telling them why I've done
> it that way [1]. There is a trade-off here. I am willing to go another
> way in that trade-off, but let's have a realistic conversation about
> it.

I think what's realistic here is that the patch isn't going to get
committed the way you have it today, because nobody else likes that
design.  That may be harsh, but I think it's accurate.

But on the substance of the issue, I'm totally unconvinced by your
argument in the linked email.  Let's suppose you have this:

create table foo (a int, b int);
create unique index foo1 on foo (a);
create unique index foo2 on foo (a);
create unique index foo3 on foo (a) where b = 1;

Anything that conflicts in foo1 or foo2 is going to conflict in the
other one, and with foo3.  Anything that conflicts in foo3 is perhaps
also going to conflict in foo1 and foo2, or perhaps not.  Yet, if the
statement simply says ON DUPLICATE (a) UPDATE, it's entirely clear
what to do: when we hit a conflict in any of those three indexes,
update the row.  No matter which index has the conflict, updating is
the right answer, and solves the conflict in all three indexes.  I
dunno what you'd expect someone to write in your syntax to solve this
problem - presumably either (1) they can list any of the indexes that
might conflict, (2) they must list all of the indexes that might
conflict, or (3) it just doesn't work.  Whatever the actual behavior
of your patch is today, it seems to me that the conflict is,
fundamentally, over a set of column values, NOT over a particular
index.  The index is simply a mechanism for noticing that conflict.

If you want to allow this to work with expression indexes, that's not
really a problem; you can let the user write INSERT .. ON CONFLICT
(upper(foo)) UPDATE ... and match that to the index.  It wouldn't
bother me if the first version of this feature couldn't target
expression indexes anyway, but if you want to include that, having the
user mention the actual expression rather than the index name
shouldn't make much difference.

>> Also, how about making the SET clause optional, with the semantics
>> that we just update all of the fields for which a value is explicitly
>> specified:
>>
>> INSERT INTO overwrite_with_abandon (key, value)
>>     VALUES (42, 'meaning of life')
>>     ON DUPLICATE (key) UPDATE;
>>
>> While the ability to specify a SET clause there explicitly is useful,
>> I bet a lot of key-value store users would love the heck out of a
>> syntax that let them omit it when they want to overwrite.
>
> While I initially like that idea, now I'm not so sure about it [2].
> MySQL don't allow this (with ON DUPLICATE KEY UPDATE). Their REPLACE
> command allows it, since it is defined as a DELETE followed by an
> INSERT (or something like that), but I think that in general that
> feature has been a disaster for them.

Your syntax allows the exact same thing; it simply require the user to
be more verbose in order to get that behavior.  If we think that
people wanting that behavior will be rare, then it's fine to require
them to spell it out when they want it.  If we think it will be the
overwhelming common application of this feature, and I do, then making
people spell it out when we could just as well infer it is pointless.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company