Re: INSERT ON CONFLICT and RETURNING

On 03.09.2020 19:30, Marko Tiikkaja wrote:
> There's prior art on this: https://commitfest.postgresql.org/15/1241/
>
>
> .m
Ooops:(
Thank you.
I missed it.

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.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company