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

On Thu, Sep 26, 2013 at 03:33:34PM -0400, Robert Haas wrote:
> On Thu, Sep 26, 2013 at 3:07 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> > When you consider that the feature will frequently be used with the
> > assumption that updating is a much more likely outcome, it becomes
> > clear that we need to be careful about this sort of interplay.
>
> I think one thing that's pretty clear at this point is that almost any
> version of this feature could be optimized for either the insert case
> or the update case. For example, my proposal could be modified to
> search for a conflicting tuple first, potentially wasting an index
> probes (or multiple index probes, if you want to search for potential
> conflicts in multiple indexes) if we're inserting, but winning heavily
> in the update case. As written, it's optimized for the insert case.

I assumed the code was going to do the index lookups first without a
lock, and take the appropriate action, insert or update, with fallbacks
for guessing wrong.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +