Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

On Tue, Mar 17, 2015 at 6:27 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Tue, Mar 17, 2015 at 12:11 PM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>> I'm still not sure the way the speculative locking works is the best
>> approach. Instead of clearing xmin on super-deletion, a new flag on the heap
>> tuple seems more straightforward. And we could put the speculative insertion
>> token in t_ctid, instead of stashing it in the PGPROC array. That would
>> again seem more straightforward.
>
> I see the appeal of that approach. What concerns me about that
> approach is that it makes it the problem of the inserter to confirm
> its insertion, even though overwhelmingly, speculative insertions
> succeeds (the race window is small due to the pre-check). The current
> speculative token is legitimately a very short lived thing, a thing
> that I hesitate to write to disk at all. So our optimistic approach to
> inserting speculatively loses its optimism, which I don't like, if you
> know what I mean.

Modifying a shared buffer is not the same as writing to disk.

>> If I'm reading this correctly, if there are multiple indexes that match the
>> unique index inference specification, we pick the cheapest one. Isn't that
>> unstable? Two backends running the same INSERT ON CONFLICT statement might
>> pick different indexes, and the decision might change over time as the table
>> is analyzed. I think we should have a more robust rule. Could we easily just
>> use all matching indexes?
>
> Robert feel pretty strongly that there should be a costing aspect to
> this. Initially I was skeptical of this, but just did what he said all
> the same. But I've since come around to his view entirely because we
> now support partial unique indexes.

I think Heikki's concern is something different, although I am not
altogether up to speed on this and may be confused. The issue is:
suppose that process A and process B are both furiously upserting into
the same table with the same key column but, because they have
different costing parameters, process A consistently chooses index X
and process B consistently chooses index Y. In that situation, will
we deadlock, livelock, error out, bloat, or get any other undesirable
behavior, or is that A-OK?

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