Re: UPSERT wiki page, and SQL MERGE syntax

On Mon, Oct 13, 2014 at 7:46 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Come on. You've had the syntax that way for a while, multiple people
> have objected to it, and it didn't get changed. When people renewed
> their objections, you said that they were not having a "realistic
> conversation". I'm tired of getting critiqued because there's some
> fine point of one of the scores of emails you've sent on this topic
> that you feel I haven't adequately appreciated.

We're in the fine point business. Obviously the issues with partial
unique indexes *are* pretty esoteric. But worrying about these edge
cases are the kind of thing that will get us an implementation of high
enough quality to commit. They're esoteric until they affect you.

> I would like to avoid
> getting into a flame-war here where we spend a lot of time arguing
> about who should have said what in exactly what way, but I think it is
> fair to say that your emails have come across to me, and to a number
> of other people here, as digging in your heels and refusing to budge.

I am not refusing to budge (in point of fact, on this question I have
already budged; see my remarks below). I am saying: Hey, there is a
reason why you're getting push back here. The reason isn't that I like
"WITHIN unique_index_name" so much - obviously I don't. Who could?

> I don't immediately see why this would cause a problem. IIUC, the
> scenario we're talking about is:
>
> create table foo (a int, b int);
> create unique index foo1 on foo (a) where b = 1;
> create unique index foo2 on foo (a) where b = 2;
>
> If the user issues INSERT .. ON DUPLICATE (a) UPDATE, we'll fire
> before-insert triggers and then inspect the tuple to be inserted. If
> b is neither 1 nor 2, then we'll fail with an error saying that we
> can't support ON DUPLICATE without a relevant index to enforce
> uniqueness. (This can presumably re-use the same error message that
> would have popped out if the user done ON DUPLICATE (b), which is
> altogether un-indexed.) But if b is 1 or 2, then we'll search the
> matching index for a conflicting tuple; finding none, we'll insert;
> finding one, we'll do the update as per the user's instructions.

Before row insert triggers might invalidate that conclusion at the
last possible moment. So you'd have to recheck, which is just messy.

>> As always with this stuff, the devil is in the details. If we work out
>> the details, then I can come up with something that's acceptable to
>> everyone. Would you be okay with this never working with partial
>> unique indexes? That gives me something to work with.
>
> If it can't be made to work with them in a reasonable fashion, I would
> probably be OK with that, but so far I see no reason for such
> pessimism.

At the very least I think that it would be a bad use of our resources
to bend over backwards to make this work for partial unique indexes,
which is what it would take. So I will proceed with the basic idea of
naming columns, while not having that ever resolve partial unique
indexes.

> I'm considering your points in this area as well as I can, but as far
> as I can tell you haven't actually set what's bad about it, just that
> it might be hazardous in some way that you don't appear to have
> specified, and that MySQL doesn't allow it. I am untroubled by the
> latter point; it is not our goal to confine our implementation to a
> subset of MySQL.

I did - several times. I linked to the discussion [1]. I said "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". And Kevin
eventually said of not supporting partial unique indexes: "That seems
like the only sensible course, to me". At which point I agreed to do
it that way [2]. So you've already won this argument. All it took was
looking at my reasons for doing things that way from my perspective.
If there has been digging of heals, you should consider that it might
be for a reason, even if on balance you still disagree with my
conclusion (which was clearly not really a firm conclusion in this
instance anyway). That's all I mean here.

I have been successful at talking you out of various approaches to
this problem multiple times. This is not simple intransigence.

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

[2] http://www.postgresql.org/message-id/CAM3SWZStdChN6-ieJbc20OGD8TwmZ6-um6O8Gz2BOFzXn9YFVg@mail.gmail.com
--
Peter Geoghegan