Re: UPSERT wiki page, and SQL MERGE syntax

On Fri, Oct 10, 2014 at 11:05 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Anything we do about triggers will by definition be novel. Right now,
> we have INSERT, UPDATE, and DELETE. If we add a new operation,
> whether it's called UPSERT or MERGE or FROB, or if we add a flag to
> insert that makes it possibly do something other than inserting (e.g.
> INSERT OR UPDATE), or if we add a flag to update that makes it do
> something other than updating (e.g. UPDATE or INSERT), then some
> people's triggers are going to get broken by that change no matter how
> we do it. When the new operation is invoked, we can fire the insert
> triggers, the update triggers, some new kind of trigger, or no trigger
> at all - and any decision we make there will not in all cases be
> backward-compatible. We can try to minimize the damage (and we
> probably should) but we can't make it go to zero.

+1. It's inevitable that someone isn't going to be entirely happy with
whatever we do. Let's be realistic about that, while minimizing the
risk.

> I actually like this syntax reasonably well in some ways, but I don't
> like that we're mentioning the index name, and the CONFLICTING()
> notation is decidedly odd. Maybe something like this:

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.

> 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.

[1] http://www.postgresql.org/message-id/CAM3SWZQpGf6_ME6D1vWqdCFadD7Nprdx2JrE=Hcf=93KXeHY4g@mail.gmail.com

[2] http://www.postgresql.org/message-id/CAM3SWZT=VXBJ7QKAidAmYbU40aP10udSqOOqhViX3Ykj7WBv9A@mail.gmail.com
--
Peter Geoghegan