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