Re: INSERT ON CONFLICT and RETURNING

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