On 9/3/20 6:52 PM, Konstantin Knizhnik wrote:
But frankly speaking I still didn't find answer for my question in this
thread: what are the dangerous scenarios with ON CONFLICT DO
NOTHING/SELECT.
Yes, record is not exclusively locked. But I just want to obtain value
of some column which is not a source of conflict. I do not understand
what can be wrong if some
other transaction changed this column.
And I certainly can't agree with Peter's statement:
> Whereas here, with ON CONFLICT DO SELECT,
> I see a somewhat greater risk, and a much, much smaller benefit. A
> benefit that might actually be indistinguishable from zero.
From my point of view it is quite common use case when we need to
convert some long key to small autogenerated record identifier.
Without UPSERT we have to perform two queries instead of just one . And
even with current implementation of INSERT ON CONFLICT...
we have to either perform extra lookup, either produce new (useless)
tuple version.
I have no idea about the potential risks here since I am not very
familiar with the ON CONFLICT code, but I will chime in and agree that
this is indeed a common use case. Selecting and taking a SHARE lock
would also be a nice feature.
Andreas