Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Tue, Sep 24, 2013 at 10:15 PM, Peter Geoghegan <pg@heroku.com> wrote:
> Well, I think we can rule out value locks that are held for the
> duration of a transaction right away. That's just not going to fly.

I think I agree with that.  I don't think I remember hearing that proposed.

> If we're really lucky, maybe the value locking stuff can be
> generalized or re-used as part of a btree index insertion buffer
> feature.

Well, that would be nifty.

>> Also, I tend to think that we might want to define
>> the operation as a REPLACE-type operation with respect to a certain
>> set of key columns; and so we'll do the insert-or-update behavior with
>> respect only to the index on those columns and let the chips fall
>> where they may with respect to any others.  In that case this all
>> becomes much less urgent.
>
> Well, MySQL's REPLACE does zero or more DELETEs followed by an INSERT,
> not try an INSERT, then maybe mark the heap tuple if there's a unique
> index dup and then go UPDATE the conflicting tuple. I mention this
> only because the term REPLACE has a certain baggage, and I feel it's
> important to be careful about such things.

I see.  Well, we could try to mimic their semantics, I suppose.  Those
semantics seem like a POLA violation to me; who would have thought
that a REPLACE could delete multiple tuples?  But what do I know?

> The only way that's going to work is if you say "use this unique
> index", which will look pretty gross in DML. That might actually be
> okay with me if we had somewhere to go from there in a future release,
> but I doubt that's the case. Another issue is that I'm not sure that
> this helps Andres much (or rather, clients of the logical changeset
> generation infrastructure that need to do conflict resolution), and
> that matters a lot to me here.

Yeah, it's kind of awful.

>> Suppose we define the operation as REPLACE rather than INSERT...ON
>> DUPLICATE KEY LOCK FOR UPDATE.  Then we could do something like this:
>>
>> 1. Try to insert a tuple.  If no unique index conflicts occur, stop.
>> 2. Note the identity of the conflicting tuple and mark the inserted
>> heap tuple dead.
>> 3. If the conflicting tuple's inserting transaction is still in
>> progress, wait for the inserting transaction to end.
>
> Sure, this is basically what the code does today (apart from marking a
> just-inserted tuple dead).
>
>> 4. If the conflicting tuple is dead (e.g. because the inserter
>> aborted), start over.
>
> Start over from where? I presume you mean the index tuple insertion,
> as things are today. Or do you mean the very start?

Yes, that's what I meant.

>> 5. If the conflicting tuple's key columns no longer match the key
>> columns of the REPLACE operation, start over.
>
> What definition of equality or inequality?

Binary equality, same as we'd use to decide whether an update can be done HOT.

>> 7. Update the tuple, even though it may be invisible to our snapshot
>> (a deliberate MVCC violation!).
>
> I realize that you just wanted to sketch a design, but offhand I think
> that the basic problem with what you describe is that it isn't
> accepting of the inevitability of there being a disconnect between
> value and row locking. Also, this doesn't fit with any roadmap for
> getting a real upsert,

Well, there are two separate issues here: what to do about MVCC, and
how to do the locking.  From an MVCC perspective, I can think of only
two behaviors when the conflicting tuple is committed but invisible:
roll back, or update it despite it being invisible.  If you're saying
you don't like either of those choices, I couldn't agree more, but I
don't have a third idea.  If you do, I'm all ears.

In terms of how to do the locking, what I'm mostly saying is that we
could try to implement this in a way that invents as few new concepts
as possible.  No promise tuples, no new SLRU, no new page-level bits,
just index tuples and heap tuples and so on.  Ideally, we don't even
change the WAL format, although step 2 might require a new record
type.  To the extent that what I actually described was at variance
with that goal, consider it a defect in my explanation rather than an
intent to vary.  I think there's value in considering such an
implementation because each new thing that we have to introduce in
order to get this feature is a possible reason for it to be rejected -
for modularity reasons, or because it hurts performance elsewhere, or
because it's more code we have to maintain, or whatever.

Now, what I hear you saying is, gee, the performance of that might be
terrible.  I'm not sure that I believe that, but it's possible that
you're right. Much seems to depend on what you think the frequency of
conflicts will be, and perhaps I'm assuming it will be low while
you're assuming a higher value.  Regardless, if the performance of the
sort of implementation I'm talking about would be terrible (under some
agreed-upon definition of what terrible means in this context), then
that's a good argument for not doing it that way.  I'm just not
convinced that's the case.

Basically, if there's a way we can do this without changing the
on-disk format (even in a backward-compatible way), I'd be strongly
inclined to go that route unless we have a really compelling reason to
believe it's going to suck (or be outright impossible).

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