Re: UPSERT wiki page, and SQL MERGE syntax

On Mon, Oct 13, 2014 at 2:02 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>> 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.

I can't imagine that you'd decide which index to use and then change
your mind when you turn out to be wrong. I think rather you'd compute
a list of possibly-applicable indexes based on the ON DUPLICATE column
list, and then, after firing before-insert triggers, check whether
there's one that will definitely work. If there's a non-partial
unique index on the relevant columns, then you can put any single such
index into the list of possibly-usable indexes and leave the rest out;
otherwise, you include all the candidates and pick between them at
runtime.

If that seems too complicated, leave it out for v1: just insist that
there must be at least one unique non-partial index on the relevant
set of columns.

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

There seems to be some confusion here. This part was about this syntax:

>>>> INSERT INTO overwrite_with_abandon (key, value)
>>>> VALUES (42, 'meaning of life')
>>>> ON DUPLICATE (key) UPDATE;

That's a different issue from naming indexes.

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