Re: Promise index tuples for UPSERT

On Tue, Oct 7, 2014 at 6:06 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I'm not up on the details of what Peter's patch does with heavyweight
> locking, but I'd say it this way: if the patch uses heavyweight
> locking routinely, that's probably not going to scale well[1]. If
> the patch detects possible conflicts and uses heavyweight locking only
> in those cases and for the specific purpose of untangling those
> conflicts, then that might well be OK.

The patch opportunistically tries to use shared buffer locks when a
conflict is expected, when we restart (but only on the unique index
where a conflict was detected). So in the event of a lot of
near-conflicts, the hwlock traffic is quite modest. That, combined
with the fact that it uses what I've called "an index scan with an
identity crisis" (could be a near-insertion + hwlock in advance of
insertion proper, or just something akin to a regular index scan)
makes it perform best (at least with one or two unique indexes, which
is what I tested a few months back). It does not have a pre-check that
is always wasted with insertion-heavy workloads.

Now, we're not talking about a huge advantage here (I should re-test
that). And, in case I wasn't clear: I have misgivings about all 3
designs. Like Simon, I think it is appropriate that we figure out our
exact requirements using the two working prototype patches. Although,
right now #1 and #2 (the prototypes) seem quite comparable, that might
just be down to a failure of imagination. It's hard to be completely
confident about something like that.

--
Peter Geoghegan