Re: Promise index tuples for UPSERT

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Peter Geoghegan <pg(at)heroku(dot)com>, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Andres Freund <andres(at)2ndquadrant(dot)com>
Subject: Re: Promise index tuples for UPSERT
Date: 2014-10-07 13:06:52
Message-ID: CA+Tgmob7-qwOD-=9J4Uf6hu0AdZmfR6EUb6C+eV0+a9F+TsVkQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Oct 7, 2014 at 8:33 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 7 October 2014 03:31, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>>> It may be that people on reading this now believe Peter's HW locking
>>> approach is the best. I'm happy to go with consensus.
>>
>> I bet you didn't think that you'd say that a week ago. :-)
>
> You're right, because last week I thought heavyweight locking sucks
> and I still think that; I haven't said it is the best.
>
> What we've just discovered is that we're locking 100% of the time, but
> its not needed in 99.9% of cases and is arguable in the 0.1% case -
> not "required" at all.
>
> The price of avoiding that rare and possibly erroneous condition seems
> high to me.
>
> Is there a way of detecting that we are updating a unique constraint
> column and then applying the HW locking only in that case? Or can we
> only apply locking when we have multiple unique constraints on a
> table?
> If so, I would withdraw any objection to the HW locking technique.

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.

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

[1] As evidence, I offer the fact that removing 1 of 2 places where
heavyweight locking is used in hash indexes resulted in a large
performance gain at high client counts. See commit
76837c1507cb5a5a0048046233568447729e66dd.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Feike Steenbergen 2014-10-07 13:24:57 Re: Add regression tests for autocommit-off mode for psql and fix some omissions
Previous Message Heikki Linnakangas 2014-10-07 12:52:24 pg_upgrade, locale and encoding