Re: Making joins involving ctid work for the benefit of UPSERT

On Wed, Jul 30, 2014 at 10:36 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> You've convinced me that only indexable predicates can be sensibly
> used here. I'm not sure that's the same as saying that upserts are
> driven by inserts, though. I'd tend to interpret that to mean
> insert-the-heap-tuple-first, but I think what you're doing is
> check-the-indexes-first. The terminology in this area is tricky.

I'm glad. Indeed, I am proposing checking/locking indexes first, but
that is actually pretty similar to what Heikki did in his prototype
(insert a heap tuple first), in that those heap tuples were
conceptually speculative/optimistic "value locks". Heikki's need to
"super delete" in the event of a conflict (by setting the speculative
heap tuple's xmin to InvalidTransactionId) demonstrates that.
Furthermore, Heikki's last revision ended up having places like
_bt_checkunique() looking in the procarray to see if the xact to wait
on was still around and had a "speculative token", so that we could
wait on that to be released rather than the whole xact to commit/abort
(avoiding unprincipled deadlocking hinges upon this):

+ /*
+ * If it's a speculative insertion, wait for it to finish (ie.
+ * to go ahead with the insertion, or kill the tuple). Otherwise
+ * wait for the transaction to finish as usual.
+ */
+ if (speculativeToken)
+ SpeculativeInsertionWait(xwait, speculativeToken);
+ else
+ XactLockTableWait(xwait);

So, as I said, what he ended up with was actually very close to what I
wanted to do. I felt it was more logical to just use an index page
lock, given the fact that they have been used before, and given the
limited code footprint (although I do accept that in general altering
the nbtree code is best avoided). But Heikki did ultimately understand
the considerations that informed my design, and so ultimately IMV the
disagreement there was more or less just on that one detail. (Not that
the rest of the patch was perfect or anything, but it was useful to
have my design *understood*). You can hopefully see why I'd
characterize both of those designs as insertion driven.

The terminology is very tricky here, though. It might even be the
trickiest aspect to the whole thing, because at times I've had a hard
time communicating what I mean effectively on list. Anyway, I'll be
sure to emphasize the distinction in future.

--
Peter Geoghegan