Re: Unnecessary FOR UPDATE lock instead of possible FOR NO KEY UPDATE lock in an UPSERT statement

On Fri, Oct 2, 2020 at 5:14 PM lights go out <enderstd(at)gmail(dot)com> wrote:
> So in case 3 we rewrite the key part of the tuple with the same value
> and get the stronger FOR UPDATE lock.
> While in case 1 we do the same but acquire a softer FOR NO KEY UPDATE lock
> which is the correct behavior since we haven't actually changed the key.
>
> This is inconsistent.

It's not inconsistent. The excluded.* pseudo row is not the same thing
as the target row. I believe that you would see behavior consistent
with the plain UPDATE case (i.e. tuple lock strength "No Key Update")
you changed the ON CONFLICT ... DO UPDATE to update the target row
using the target row itself (in the UPDATE's target list). You can use
AS to create an alias for the target table so its row can be updated
using itself. This is what you actually did with your UPDATE example.

Of course, that isn't very helpful -- nobody writes queries like your
UPDATE example for obvious reasons. But the fact remains: there is no
inconsistency between UPDATE and ON CONFLICT ... DO UPDATE in evidence
here.

> I expect UPSERT query in case 3 to grab a FOR NO KEY UPDATE lock
> because SET (a, b) = (excluded.a, excluded.b) is not actually modifying the key.
>
> Unnecessarily stronger locks increase lock contentions,
> for example blocking FK constraint checks.

You're assuming that it's impossible for two equal values to be
distinct from each other. But that is possible, at least in some
cases. For example, with a unique index on a numeric column the value
'5.00' is visibly distinct from '5', but the values are nevertheless
equal. You could therefore have a conflict in which
exluded.numeric_col and target.numeric_col are visibly different, in a
way that the user might care about.

It would be possible in principle to optimize this case. But there are
numerous hard problems to solve to do so (not just the one I
mentioned), so I wouldn't expect that to happen. I would just rewrite
the query.

--
Peter Geoghegan